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!

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

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

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.

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.

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

Simple steps wonderfull understanding. Thanks buddy

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

Excelent examples! Thanks!

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

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

Dude, you saved my life. Thanks a lot!

Great tip and very clearly explained, thanks

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

Congratulations!! Very good explanation!

Thank you for the video!

Superb 👍👍👍👍👍👍

This video really helped me. Thank you Mark.

One of the best explanations. Thank you

@silverfunnel6819

3 жыл бұрын

Yes, very easy to replicate.

This is awesome!! Thanks for uploading.

Such a great video !!! Thanks

It is very important introduction. thank you

Thank you very much! Very nice!

Thank you Mark, that was so helpful

Thanks for sharing! 👍

Genau das habe ich seit Tagen gesucht ;-)

Really good one!!

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😉

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 good， thanks👍🏻

great sir , i am too junior in it

i need more of this

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, Thanks for sharing. I have one question can we append the data Through parameters in powerquery..

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

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

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

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'

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.

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

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.

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.

Can we prompt Parameter Values as a drop down list?

@markmoore23

13 күн бұрын

Yes you can

@muhammadasad729

13 күн бұрын

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

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/

Close in camera in video recorder.