PowerQuery Parameters

Рет қаралды 40,907

Mark Moore

Күн бұрын

Пікірлер: 64

@yosefk2864 2 жыл бұрын

Great explanation! Changing the privacy setting is what made the difference from frustration to joy. Thanks.

@sjd7aa 2 жыл бұрын

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 жыл бұрын

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.

@MrDhunpagla 3 жыл бұрын

Superb 👍👍👍👍👍👍

@joelabacherli1310 Жыл бұрын

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.

@jonathanvillafana5726 Жыл бұрын

Thank you! This did the trick for making a SQL query dynamic for date range parameters.

@sovsel 3 жыл бұрын

One of the best explanations. Thank you

@silverfunnel6819 3 жыл бұрын

Yes, very easy to replicate.

@mikedbman 2 жыл бұрын

I have been looking for this exact video for months! Thank goodness you created it!

@silverfunnel6819 3 жыл бұрын

This is the easiest method, in my eyes, thank you!

@YogeshSharma-ui7xx 3 жыл бұрын

Simple steps wonderfull understanding. Thanks buddy

@sedarathnadcd 2 жыл бұрын

It is very important introduction. thank you

@kunalrules608 Жыл бұрын

This video really helped me. Thank you Mark.

@guilhermeiezzimoscainacio6591 3 жыл бұрын

Dude, you saved my life. Thanks a lot!

@cemisovs 5 ай бұрын

Thank you for the video!

@kerrianguiano 3 жыл бұрын

Awesome, this is exactly what I was trying to figure out how to do!

@vzmol 2 жыл бұрын

Thank you! You explained all I needed to know - and you explained it beautifully. Made my day, honestly. Good day to you :)

@luizantonionogueira8838 3 жыл бұрын

Congratulations!! Very good explanation!

@Boz1836 2 жыл бұрын

Great tip and very clearly explained, thanks

@dancorwin9232 2 жыл бұрын

This was exactly what I needed. Thanks a bunch!!

@Super8822 Жыл бұрын

This is awesome!! Thanks for uploading.

3 жыл бұрын

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.

@khalidmajeed2886 Жыл бұрын

great sir , i am too junior in it

@duncanwil 2 жыл бұрын

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!

@zuhairalmutawa9941 2 жыл бұрын

Thank you Mark, that was so helpful

@mrchesitostar7652 2 жыл бұрын

Such a great video !!! Thanks

@KlausTrapp 3 жыл бұрын

Genau das habe ich seit Tagen gesucht ;-)

@dougmphilly Жыл бұрын

i need more of this

@paul.tran_3388 3 жыл бұрын

Excelent examples! Thanks!

@joedaguro 2 жыл бұрын

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😉

@FRANKWHITE1996 2 жыл бұрын

Thanks for sharing! 👍

@bartasoi5492 3 жыл бұрын

Thank you very much! Very nice!

@sandhiaraja7921 3 жыл бұрын

Really good one!!

@kebincui 2 жыл бұрын

Very good， thanks👍🏻

@3danim8r1 2 жыл бұрын

Great, Thanks for sharing. I have one question can we append the data Through parameters in powerquery..

@vidhigarg9218 2 жыл бұрын

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'

@buybuydandavis Жыл бұрын

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 "

@markmoore23 Жыл бұрын

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

@zohaibazam7105 3 жыл бұрын

What if I want to see all month data. I mean if I said my parameters are null show all data

@TheLaopi 2 жыл бұрын

Very interesting Can you share the file ton truc again, thanks

@zzzzzzzzzzzzzzzz9 3 жыл бұрын

I'm just trying to find the benefit of using this than filter from a table

3 жыл бұрын

Better use a data slicer instead of filter.

@markmoore23 3 жыл бұрын

A slicer/filter will hide rows in the data table. A PQ parameter will prevent the data from loading into Excel.

@abdullahquhtani4247 Жыл бұрын

Will you Reply to suggestions?

@markmoore23 Жыл бұрын

Sure. What do you suggest?

@abdullahquhtani4247 Жыл бұрын

@@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 🙏🏻

@markmoore23 Жыл бұрын

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/

@carlosduranurenda915 2 жыл бұрын

nice trick but only works for specific values, dont work for filters like "amount >= 1000"

@muhammadasad729 Ай бұрын

Can we prompt Parameter Values as a drop down list?

@markmoore23 Ай бұрын

Yes you can

@muhammadasad729 Ай бұрын

@@markmoore23 great. Would've been useful if we can select instead of typing (wrong).

@daXcel7448 4 жыл бұрын

Thanks. What if I want to see both Jan and Feb data

@markmoore23 4 жыл бұрын

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.

@Denis8492 3 жыл бұрын

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

3 жыл бұрын

Use a data slicer instead of parameters.

@rakshitharamesh3161 3 жыл бұрын

@@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.

@markmoore23 3 жыл бұрын

@@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.

@joshuaprets2044 2 жыл бұрын

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."

@markmoore23 2 жыл бұрын

Open PQ. File > Query Options > in Global Section choose Privacy. Select 'Always ignore Privacy Level settings'. Click OK.

@joshuaprets2044 2 жыл бұрын

@@markmoore23 Thanks. I literally stopped watching the video a minute too soon.