This was EXACTLY what I've been trying to figure out. That was an easy and elegant parameter solution. I tweaked it a bit: instead of typing the entry, my choice is a drop-down list named from another table to simplify selection, and it works bang-on. Wish I could upvote you more than once for this vid. Many thanks!
3 ปีที่แล้ว +2
Parameters are great! A very good usage for parameters: I am working in mass mailing service. For all client mailings, we include meta data for each mailing for the print center. One of the meta data is for example the delivery speed "Priority Post" or "Standard Post". So, I have a dropdown with these two options and the selected option gets promoted to a parameter "DeliveryParam". In PQ, I create a column "Delivery Parameter" with the programming: = DeliveryParam Or you have a dropdown "PERSONAL SALUTATION" with the options "YES" and "NO" promoted to a parameter "PersSalutation". In PQ, you create a column "SALUTATION" with the programming: if PersSalutation = "YES" then "Dear "&[FIRST_NAME] else "Dear "&[PREFIX]&" "&[LAST_NAME] PQ has also a very comfortable way to create parameters without the method described in the video. The user can define parameters directly in PQ rather than in the table on the Excel sheet. A parameter table on the sheet is faster accidentally deleted than parameters set in Power Query. But it all depends on personal preferences and workflow definitions.
I would use a data slicer for this purpose, rather than parameters. So, you just select the name of the sales person in the slicer. The parameter, i would use for setting for example for an interest rate to make dynamic calculations. But file path, of course, i agree, is a predestined for parameter usage.
Very helpful, thank you, I had this issue whenever I move to another station in the network where I need to change the drive path in map network drive😉
Hi, have an urgent question. The parameter is not working when I'm writing it in a Snowflake query. It is saying that ' Invalid identifier PersonParameter'
Any way to make a query in the AdvancedEditor based on a cell range? " select * from SomeTable ST where ST.Matchfield in (LocalExcelRange) " or " select * from LocalExcelTable LET join ExtermalSQLtable EST on LET.JoinField=EST.JoinField "
Yes but the named range needs to be in another workbook. In the query workbook, PQ will show you all the tables (which are named ranges) and all the non-table named ranges. You can choose the named range. I tested it and the M command looks like this: = Source{[Item="test",Kind="DefinedName"]}[Data] where 'test' is the named range
@@markmoore23 in case I want to remove filter and list all of records? Is there a way for example to add list all to parameters and use if statement with that 🤔 I have the logic but want to listen from experts like you. Thank you 🙏🏻
You will need to add a new step, manually, the new step will be an IF statement that tests for the 'All records' value. Something like: = if AccountFilter = "" then #"Changed Type" else #"Filtered Rows" Where #"Changed Type" is the previous unfiltered step and #"Filtered Rows" is the previous filtered step. Look at this page for more instructions: exceloffthegrid.com/filter-all-in-power-query/
That's a bit tricky. Power Query doesn't have an IN keyword like SQL but there are a few ways to do this. 1 - You can write two queries, one for each month and then append them. This falls apart when you have multiple conditions though. 2 - You can create a new calculated column and use and Excel formula that returns a tag of some sort (i.e. "x", "ok") for the months of interest and then filter on that value. 3 - Create a new table with the months of interest. Load that into a new query. This is now your parameter table/query. Use a Merge query to merge the data and the new parameter query. Use an inner join. This is the most robust solution in that you can have as many months as needed, and you can change them in Excel.
PQ have one trick when you are pointing source to the excel workbook. You actually can choose source above list of all sheets, parent folder tree and that gives you table with all content of your workbook (hidden sheets, visible sheets, named tables...). Maybe this part is best starting point to expand desired tables and merge them
@@rakshitharamesh3161 You'll need to create another table with two columns, the ID column and a 'keep' column. The keep column can be manually populated with Y, 1 or whatever tag you want to use to flag the ID's to keep. Import that into PQ. Create a merge query with the ID as a common field. Expand the new column, filter for the keep tag you previously made.
I'm trying to use this to connect to a csv file and get an error "Formula.Firewall: Query 'xx' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
I watched the video and thought your explanation was good to watch. So, I wanted to emulate what you did. I got right to the point where you edit the Navigation step ... only to find there is no navigation step now!
Great explanation! Changing the privacy setting is what made the difference from frustration to joy. Thanks.
Brilliant. Keeping it in excel is huge. This is precisely what I have been looking for and even more. Thank you for this valuable information.
This was EXACTLY what I've been trying to figure out. That was an easy and elegant parameter solution. I tweaked it a bit: instead of typing the entry, my choice is a drop-down list named from another table to simplify selection, and it works bang-on. Wish I could upvote you more than once for this vid. Many thanks!
Parameters are great! A very good usage for parameters: I am working in mass mailing service. For all client mailings, we include meta data for each mailing for the print center. One of the meta data is for example the delivery speed "Priority Post" or "Standard Post". So, I have a dropdown with these two options and the selected option gets promoted to a parameter "DeliveryParam". In PQ, I create a column "Delivery Parameter" with the programming: = DeliveryParam
Or you have a dropdown "PERSONAL SALUTATION" with the options "YES" and "NO" promoted to a parameter "PersSalutation". In PQ, you create a column "SALUTATION" with the programming: if PersSalutation = "YES" then "Dear "&[FIRST_NAME] else "Dear "&[PREFIX]&" "&[LAST_NAME]
PQ has also a very comfortable way to create parameters without the method described in the video. The user can define parameters directly in PQ rather than in the table on the Excel sheet. A parameter table on the sheet is faster accidentally deleted than parameters set in Power Query. But it all depends on personal preferences and workflow definitions.
Thank you! This did the trick for making a SQL query dynamic for date range parameters.
I have been looking for this exact video for months! Thank goodness you created it!
This video really helped me. Thank you Mark.
One of the best explanations. Thank you
Yes, very easy to replicate.
This is awesome!! Thanks for uploading.
Dude, you saved my life. Thanks a lot!
This is the easiest method, in my eyes, thank you!
Simple steps wonderfull understanding. Thanks buddy
Thank you! You explained all I needed to know - and you explained it beautifully. Made my day, honestly. Good day to you :)
Awesome, this is exactly what I was trying to figure out how to do!
This was exactly what I needed. Thanks a bunch!!
It is very important introduction. thank you
I would use a data slicer for this purpose, rather than parameters. So, you just select the name of the sales person in the slicer. The parameter, i would use for setting for example for an interest rate to make dynamic calculations.
But file path, of course, i agree, is a predestined for parameter usage.
Congratulations!! Very good explanation!
Great tip and very clearly explained, thanks
Superb 👍👍👍👍👍👍
Thank you Mark, that was so helpful
Such a great video !!! Thanks
Thank you for the video!
Excelent examples! Thanks!
Genau das habe ich seit Tagen gesucht ;-)
Very helpful, thank you, I had this issue whenever I move to another station in the network where I need to change the drive path in map network drive😉
Thank you very much! Very nice!
Really good one!!
great sir , i am too junior in it
Thanks for sharing! 👍
Great, Thanks for sharing.
I have one question can we append the data Through parameters in powerquery..
Very good， thanks👍🏻
i need more of this
Hi, have an urgent question. The parameter is not working when I'm writing it in a Snowflake query. It is saying that ' Invalid identifier PersonParameter'
Any way to make a query in the AdvancedEditor based on a cell range?
" select * from SomeTable ST where ST.Matchfield in (LocalExcelRange) "
or
" select * from LocalExcelTable LET join ExtermalSQLtable EST on LET.JoinField=EST.JoinField "
Yes but the named range needs to be in another workbook. In the query workbook, PQ will show you all the tables (which are named ranges) and all the non-table named ranges. You can choose the named range. I tested it and the M command looks like this: = Source{[Item="test",Kind="DefinedName"]}[Data]
where 'test' is the named range
What if I want to see all month data. I mean if I said my parameters are null show all data
Will you Reply to suggestions?
Sure. What do you suggest?
@@markmoore23 in case I want to remove filter and list all of records? Is there a way for example to add list all to parameters and use if statement with that 🤔
I have the logic but want to listen from experts like you.
Thank you 🙏🏻
You will need to add a new step, manually, the new step will be an IF statement that tests for the 'All records' value.
Something like: = if AccountFilter = "" then #"Changed Type" else #"Filtered Rows"
Where #"Changed Type" is the previous unfiltered step and #"Filtered Rows" is the previous filtered step.
Look at this page for more instructions: exceloffthegrid.com/filter-all-in-power-query/
Can we prompt Parameter Values as a drop down list?
Yes you can
@@markmoore23 great. Would've been useful if we can select instead of typing (wrong).
Thanks. What if I want to see both Jan and Feb data
That's a bit tricky. Power Query doesn't have an IN keyword like SQL but there are a few ways to do this.
1 - You can write two queries, one for each month and then append them. This falls apart when you have multiple conditions though.
2 - You can create a new calculated column and use and Excel formula that returns a tag of some sort (i.e. "x", "ok") for the months of interest and then filter on that value.
3 - Create a new table with the months of interest. Load that into a new query. This is now your parameter table/query. Use a Merge query to merge the data and the new parameter query. Use an inner join. This is the most robust solution in that you can have as many months as needed, and you can change them in Excel.
PQ have one trick when you are pointing source to the excel workbook. You actually can choose source above list of all sheets, parent folder tree and that gives you table with all content of your workbook (hidden sheets, visible sheets, named tables...). Maybe this part is best starting point to expand desired tables and merge them
Use a data slicer instead of parameters.
@@markmoore23 I am also on same lookout. Could you please elaborate on the 3rd point? FYI my parameter is an ID so it is not static.
@@rakshitharamesh3161 You'll need to create another table with two columns, the ID column and a 'keep' column. The keep column can be manually populated with Y, 1 or whatever tag you want to use to flag the ID's to keep. Import that into PQ. Create a merge query with the ID as a common field. Expand the new column, filter for the keep tag you previously made.
I'm just trying to find the benefit of using this than filter from a table
Better use a data slicer instead of filter.
A slicer/filter will hide rows in the data table. A PQ parameter will prevent the data from loading into Excel.
nice trick but only works for specific values, dont work for filters like "amount >= 1000"
Very interesting Can you share the file ton truc again, thanks
I'm trying to use this to connect to a csv file and get an error "Formula.Firewall: Query 'xx' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
Open PQ. File > Query Options > in Global Section choose Privacy. Select 'Always ignore Privacy Level settings'. Click OK.
@@markmoore23 Thanks. I literally stopped watching the video a minute too soon.
I watched the video and thought your explanation was good to watch. So, I wanted to emulate what you did. I got right to the point where you edit the Navigation step ... only to find there is no navigation step now!
Close in camera in video recorder.