Great explanation! Changing the privacy setting is what made the difference from frustration to joy. Thanks.
@sjd7aa2 жыл бұрын
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.
@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.
@sovsel4 жыл бұрын
One of the best explanations. Thank you
@silverfunnel68193 жыл бұрын
Yes, very easy to replicate.
@mikedbman2 жыл бұрын
I have been looking for this exact video for months! Thank goodness you created it!
@MrDhunpagla3 жыл бұрын
Superb 👍👍👍👍👍👍
@silverfunnel68193 жыл бұрын
This is the easiest method, in my eyes, thank you!
@kerrianguiano3 жыл бұрын
Awesome, this is exactly what I was trying to figure out how to do!
Thank you! You explained all I needed to know - and you explained it beautifully. Made my day, honestly. Good day to you :)
@dancorwin92322 жыл бұрын
This was exactly what I needed. Thanks a bunch!!
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.
@Super8822 Жыл бұрын
This is awesome!! Thanks for uploading.
@Boz18362 жыл бұрын
Great tip and very clearly explained, thanks
@zuhairalmutawa99412 жыл бұрын
Thank you Mark, that was so helpful
@khalidmajeed2886 Жыл бұрын
great sir , i am too junior in it
@duncanwil2 жыл бұрын
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!
@mrchesitostar76522 жыл бұрын
Such a great video !!! Thanks
@paul.tran_33883 жыл бұрын
Excelent examples! Thanks!
@bartasoi54923 жыл бұрын
Thank you very much! Very nice!
@dougmphilly Жыл бұрын
i need more of this
@joedaguro2 жыл бұрын
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😉
@KlausTrapp3 жыл бұрын
Genau das habe ich seit Tagen gesucht ;-)
@FRANKWHITE19962 жыл бұрын
Thanks for sharing! 👍
@sandhiaraja79213 жыл бұрын
Really good one!!
@3danim8r12 жыл бұрын
Great, Thanks for sharing. I have one question can we append the data Through parameters in powerquery..
@kebincui3 жыл бұрын
Very good, thanks👍🏻
@vidhigarg92182 жыл бұрын
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'
@zohaibazam71053 жыл бұрын
What if I want to see all month data. I mean if I said my parameters are null show all data
@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
@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/
@TheLaopi2 жыл бұрын
Very interesting Can you share the file ton truc again, thanks
@zzzzzzzzzzzzzzzz93 жыл бұрын
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.
@markmoore233 жыл бұрын
A slicer/filter will hide rows in the data table. A PQ parameter will prevent the data from loading into Excel.
@carlosduranurenda9152 жыл бұрын
nice trick but only works for specific values, dont work for filters like "amount >= 1000"
@muhammadasad7292 ай бұрын
Can we prompt Parameter Values as a drop down list?
@markmoore232 ай бұрын
Yes you can
@muhammadasad7292 ай бұрын
@@markmoore23 great. Would've been useful if we can select instead of typing (wrong).
@joshuaprets20442 жыл бұрын
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."
@markmoore232 жыл бұрын
Open PQ. File > Query Options > in Global Section choose Privacy. Select 'Always ignore Privacy Level settings'. Click OK.
@joshuaprets20442 жыл бұрын
@@markmoore23 Thanks. I literally stopped watching the video a minute too soon.
@daXcel74484 жыл бұрын
Thanks. What if I want to see both Jan and Feb data
@markmoore234 жыл бұрын
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.
@Denis84924 жыл бұрын
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.
@rakshitharamesh31613 жыл бұрын
@@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.
@markmoore233 жыл бұрын
@@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.