Excel Magic Trick 1349: Power Query with Input Variables from Excel Sheet to Extract Records

[@jhanawa]

This was awesome. I was able to create a table, pull it in, convert it to a list and use List.Contains in my power query. This allows me to give my users control over some of the variables in the reporting!!!

[@zt.5677]

I have opened a new playlist to save the Power Query Magic Tricks that I cover. In addition to that, and the by default in playlist videos: E-DAB, MSPTDA, Excel Dragons etc, I have two: one: ExcelIsFun general and one: ExcelIsFun Finance. Those Finance videos are great: you put just as much effort into teaching finance as into teaching Excel. And hand-written notes! Thank you.

[@excelisfun]

Yah, and it's funny: I made those finance videos 12 years ago, back when I did not editing... lol But the cash flow analysis concepts and formulas in finance are still all the same. In all my teaching career - finance is my favorite class to teach, but the department I work in got rid of those class and I got moved to more data analysis classes and so I have not taught it since way back then... I miss it, but I am certainly glad you are enjoying it : )

[@zt.5677]

@@excelisfun I sure do.

[@sudharshanr4997]

Amazing to see such videos helping folks even after 5 years after publishing! Great video as ever Mike!

[@excelisfun]

Glad it helps!

[@preetsingh2336]

Thanks for your valuable Transformation of knowledge through this video I just practically learned which you had explained 7 years ago

[@excelisfun]

Glad this helps!!!

[@TheHightower1992]

This honestly save my life on a project I am working on. If you ever think you are not helping people, know that you helped me TREMENDOUSLY.

[@joesmith5801]

Outstanding. I was able to trim a 10 MB excel file down to 40 KB. and the query runs WAY faster. OLD: SQL query results loaded into excel and VLookup to get information. NEW: Query with parameter. AWESOME.

[@michaelresau5260]

Fantastic video. I made my selection list from another power pivot table slicer and used these steps to filter all my other power query tables successfully. This video addressed my challenge directly and walked me right into a solution. Thank you!

[@excelisfun]

You are welcome so much! Thanks for the support with your comment, Thumbs Up and Sub : )

[@dcoltonbrown]

You are awesome. My go to when I am looking for an answer!

[@excelisfun]

Glad this helps!!!

[@houstonsam6163]

Just delivered something functionally similar at work using the conditional extraction worksheet formula (largely learned from you) with dynamic ranges to feed the output data to the appropriate charts. Have not yet parameterized my queries; need to learn this method now. Excel is really an amazing tool. Thanks for the consistent patient clarity you provide in teaching us.

[@excelisfun]

Glad my videos can help you! You are welcome for the consistent clarity, Sam!

[@FredrikDahlberg]

Just found this!! You are my hero! 🙏 Next step is to learn how to make the parameter for the criteria dynamic.

[@excelisfun]

Glad this helps!!!!

[@cezaryczajka9198]

Great guide. As always recorded so that you can turn off the sound and still know what and how. You can see that it is a powerful tool for Excel & PQ. Thanks to such professionals like you, we can learn a lot. Thank you very much and I look forward to more.

[@excelisfun]

You are welcome! Many more videos to come ! : )

[@CarloCatapano]

Awesome! Thanks to this video I met powerquery and managed to open a 2 GB CSV file by setting customized queries and outputs.

[@excelisfun]

Glad the video can help, Carlo!

[@paspuggie48]

As you know Mike, I always love your videos and I'm really grasping the Power of Power Query...it's BRILLIANT ! One observation I notice with most videos on KZbin is people navigating to the Data Tab then Refresh/RefreshAll etc....a more productive approach would be to add the icons to the Quick Access Toolbar...makes a huge difference to productivity...hope you agree :)

[@moranrd2000]

Hi Paul, I love to see a variety of "productivity" improvements. They really help me. And I appreciate your suggestion. I would add this thought, however. Sometimes, my own personal QAT (Quick Access Toolbar) can get too crowded, compounded by Microsoft is taking the vivid colors away compared to earlier versions. Resulting in a difficult to read icon on the QAT. Then the question becomes, will this spreadsheet be used only by you. What happens if you leave a company and the new users QAT is different. OR.... their PowerQuery knowledge is less than yours. I can even conceive of a sheet specific macro that refreshes when either Variable 1 or Variable 2 are changed. Different strokes for different folks. And we Power Excel are definitely different. :-)

[@OzduSoleilDATA]

THANK YOU! I was struggling to understand what people meant by parameters and "passing a variable" This video not only helped me understand, but also showed clearly how it's done. AMAZING. 🔥🔥🔥🔥

[@excelisfun]

Glad you like it and find it useful : )

[@gregbernard7861]

I love this guy.

[@excelisfun]

Thanks for the love. Excel IS fun ; )

[@sayaligawade7782]

Yes, Excel is really fun bcoz of people like you.....Cheers!

[@LotfyKozman]

Everyday you take us much farer to show the beauty of the legal marrage between Excel and Power Query solutions. Thanks

[@excelisfun]

You are welcome! It is amazing how Power Query is a whole new infinite part of Excel. It is vast and cannot just be learned quickly. But that is what is making it so much fun!!! : )

[@ahmedkammoun9409]

Your videos are just AMAZING ! thanks for all this effort, i'm an Architect, and this tricks helped me alot in my work , BIG thanks !

[@danielsandovalarias6192]

Wow This is exactly what I was looking for. I am trying to filter a date column based on a cell value. Amazing! I'll try it!

[@excelisfun]

Glad it helps!!!

[@supportpremier7747]

Mike When you created the button to Refresh all (Ctrl + Alt + F5). If you save this macro in the Personal Workbook you don't have to save the file as a macro file .xlsm This would allow you to use the macro when using Excel online. Let me know if that works. Also like to see some videos on limitations of Excel Online vs Excel Client.

[@elijahjns81]

Wow! This makes me feel much better about my processes. This guy is smart and there's like 98 steps. Thanks for the vid.

[@stefanh.1668]

An excellent explaination, that was in fact the very thing I just right need for my project. Many many thanks ❤

[@mattschoular8844]

You make challenging tasks so simple. That's why ExcelIsFun. Thanks Mike...

[@excelisfun]

You are welcome, Matt!!!!

[@JonathanExcels]

great video. i am watching on my phone so i cant try this out right now but three thoughts. 1) it seems like you could achieve the same result by joining(merge) the three tables in power query. 2) in ms query you could trigger a pop up window during refresh that asked a question like "which product type?" and the users input would be the criteria. i am hoping to do the same with PQ. 3) I'm a keyboard guy so I appreciate when you show the key strokes. tip: in the data validation dialog box the letter A is underlined in 'Allow' so Alt+a would take you right into the dropdown and then the letter l would jump you to 'List'.

[@Archie167]

Loved this✌🏻👏🏻👍🏻

[@excelisfun]

Glad you loved it, Archana!!!

[@amazingbandproyects3253]

Thanks for this video, I made it very well easy and powerful query on excel. Great Job! Regards from Costa Rica!

[@vijaysahal4556]

hey mic you are really great nd fantasy nd yours teaching way is so so quality wise unique 💯💯💯💯💯👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻😘plz can I sell all powerquery class video in sequence plz help me bcz I only understand your teaching way

[@ReverendHogwash]

Really nice, now enable multiple selections in the drop downs to aggregate even more data. That would be cool!

[@excelisfun]

Glad you like it : )

[@mirrrvelll5164]

Oh man...you know, every time when I open your video - first all first is to give you a like on the video, and then I am following it. Because I know I am going to learn something super useful. Thanks for this tip, I exactly needed this for my work and some tasks to automate it =)

[@marounsader318]

great trick! understanding M code is getting easier with time

[@excelisfun]

Yes, M Code is even easier than DAX Formulas!

[@altorbagh]

This was so awesome, thanks for sharing your knowledge!

[@excelisfun]

You are welcome!

[@macolloyd3242]

AWESOME !!!!! THANK YOU

[@excelisfun]

You are welcome!

[@stevennye5075]

A very good explanation of a complex topic!

[@excelisfun]

Glad it helps, Steven Nye : )

[@jesusbowls]

Thank you very much for this video. This helped me develop a useful tax calculator tool for work!

[@sumardjo]

Thank you very much for this video. This helped me develop a useful tax calculator tool for work! very much for exercise file very very tanks for sample file

[@excelisfun]

You are welcome for the video and sample file. Happy this can help, Rusdi!!!!

[@tariqcassim786]

I just got in here today, wow really love the way you go step by step

[@excelisfun]

You are welcome!

[@CrimsonTuber]

Interesting method to use variable query with power query. But this is too advanced for me. I really like the interface of the pivot table and slicer to create a variable query. I was trying to find a way to use this with a scatter plot and stumbled upon this video and your trick 1337. In the end I find it quite tedious and rather just use a dummy range of cells with a simple = formula that is linked to the pivot table. The only catch is I have to manually check that my range of dummy cell is sufficient.

[@excelisfun]

Well, that is why Excel so so great - there is a method for each of us. If it works, then that is great : )

[@dougmphilly]

omg!!! i need this

[@excelisfun]

Glad this helps!!!!

[@llezcanol]

Me gustó mucho el ejericicio¡ muy claro ¡ muchas gracias¡

[@woolly888in]

this is really helpful, helped me to understand Power Query. using the method above, power query export your result in another workbook?

[@dcos5]

The shortcuts alone would take me a few years to learn let alone everything else that's going on here. This is a God Tier Excel User Guide.

[@bernieofalia]

Very Helpful. Thanks

[@imranbhatti8580]

Hey Mike! Nice video like always. Do you have a video on how to filter the same data set between 2 dates?

[@invisible9237]

You are a life saver

[@excelisfun]

You are welcome!

[@mccalabb]

Thank you very much. This helped me alot but I cannot use this method to filter "date"

[@jorgedelgado4846]

¡Gracias!

[@excelisfun]

Thank you very much for your donation, Jorge!!!! It helps me greatly to keep making videos : ) : ) : ) : )

[@gabrieleranucci6425]

Very helpful video! However I can't understand why at 12:30 you typed AndCriteria01 = ProductCriteria and then in the Filtered rows step you replaced the hardcoded value with ProductCriteria. I would've expected to replace the hardcoded valur with AndCriteria01 as you declared that variable

[@vacilando86]

That's really awesome trainning. Thanks for it !!

[@marjensendk]

Awesome video, i will need this on a reconciliation tool that i will build in a not too distant future, thanks !

[@hassantaha3116]

Wonderful video!!!!!

[@dcoltonbrown]

Thanks!

[@excelisfun]

Thank you very much for your kind donation, D Colton Brown!!! : )

[@drsteele4749]

Mike, that's awesome stuff! Very useful. I hope that one day MS creates a ribbon button or some kind of wizard that helps create parameters - it's hard for some of us to remember syntax of M code.

[@excelisfun]

There is a Parameter button in the Power Query Ribbon Tab, but I have not learned about it yet. When I do, I will make a video.

[@RoelStein]

@@excelisfun Thankz for all your video’s. Great input. Did you made this video on the parameters topic? I couldn’t find it. Or was it still on the bucketlist?😉 I did find a video on the topic btw: kzbin.info/www/bejne/bYXJh2unibqsnLc. Looks workable. But i must test it to compare it with your solution. So the question will be: Which one is preferable..

[@alimed001]

Thank you so much for you help

[@entertainmentgalaxy971]

Beautiful... I wonder if we add unique Product and Region will it work flawlessly ?

[@csemanate]

Always useful

[@maneshzaveri6277]

Thanks - great video - Power Query and it unlimited possibilities

[@excelisfun]

You are welcome !

[@RobertLoPinto]

I am using a handcoded sql statement for my connection-only Query that gets submitted to SQL Server. I have several copies of this same query, one for each country since each country requires its own unique odbc data source (they are stored in different databases). The query has a start and end date to fetch data for a specific time frame. I use an append query to combine all of the query results into one data set and load that into the Excel data model. The challenge is everytime I edit the dates in one query I have to make the same change in all queries (to keep the dates in sync). Making matters worse, each time I make this date change the query I just edited runs for dozens of minutes since there are millions of records on the backend. Goal: Turn the date criteria in the source sql text into a reference to a start and end date on one of the Excel worksheets. This video shows brilliantly how to do that AFTER the data has been fetched as a filter. I want to dynamically change the where criteria date values in the SQL source ITSELF. I have found no solutions to this. Even if the backend used a parmetized query, how to fetch the parameter values and use them in the SQL source editor before executing the stored proc. The issue is the same. Has anyone figured out a workaround for this situation?

[@excelisfun]

I have NO idea how to do that. That dialog box that you type the SQL in, does not allow inputs from Excel. I wonder if er could author a query in M Code (using User Interface) and then get the data that way. There are two advantages: 1) Power Query usually can send the M Code back to an SQL Database and let the optimizer in the SQL Database optimize it, and then secondly 2) you can import the dates from an Excel Sheet and use that in your M Code query. I have not tried this, but I am pretty sure it would work.

[@excelisfun]

I just did an experiment and it seemed to work just fine; meaning: 1) I connected to an SQL Database using M Code and a filter for date (I used the User Interface to create M Code) , 2) then imported two variables from Excel, 3) Then inserted the variables into the M Code where the Between Date Criteria was, and 4) it updates just fine when I changed the inputs and refreshed. This is not to say that there might be other issues with your query in terms of speed of query, but the concept worked great : )

[@RobertLoPinto]

ExcelIsFun Thank you for taking the time to try that out. I have a very long, complicated and well tested sequence of SQL statements using a with clause at the top to define the dates referenced throughout several sub queries. I never used M code before but hopefully I can mimic the current output I am getting. Not sure how easy it will be to debug if things go awry. With my current SQL I write and test it thoroughly in SQL Workbench first before copy/pasting it into Excel's query editor. I'll experiment with something simple first. I hope it applies the filter server side and not client side or it won't perform well. Thanks again!

[@excelisfun]

I do not know if it applies to the filter server side or client side . If you do a test, do post back and let me know. I do not get to do a lot of connecting to SQL databases, so I am always eager to learn : )

[@marcjosephferrer3262]

this is very informative and useful. Thank you for sharing 👍😁

[@felipevega2534]

What about numeric filters, for example dates, or days ?

[@rachidelbargui9451]

I am also looking to make a date filter and especially month and year like 03/2020.

[@stephanebrizoua4954]

Great video again. the fact to link macro nd query it is powerful. But I am wodering, we could have created a power query function

[@excelisfun]

We did create a Power Query Function in this video.

[@RoelStein]

Bedankt

[@excelisfun]

Thank you very much for the donation, Roel : ) : ) : ) It helps me to keep making videos!!!

[@kennethvela2784]

Isn't it much better if you use advanced filter?

[@TheKashibaba]

Great help for adding parameter values however i tried to set date as value to filter a row before that date parameter but it didn't work. how we can filter date rows before a certain date input through parameter? Thanks a lot.

[@johanlaas3972]

Thanks for the great tutorial. Is it possible to use the input value in the source line in advanced editor. I aim to have a date parameter used in the source line so that the SQL query specifies between which dates data needs to be downloaded. Currently the entire dataset is downloaded (which is quite large and takes time) and then only filtered. Please help!!

[@himanshudalai1028]

Fantastic video & great learning. Thank you Mike for this awesome video !!

[@excelisfun]

You are welcome! I am very appreciative of your consistent support. So many people watch and learn, but so few are as amazing as you. Thanks : )

[@SanthoshAkulaqv]

Hi Is there any video that uses sql query to pull the data by providing multiple parameters in power query

[@holgerturk4817]

thank you for this tutorial

[@excelisfun]

You are welcome, Holger!!

[@syedaliraza1120]

Thank you for this awesome video. I wanted to know that is there a way to control a specific date range using input variables. like in this data we also have a date column. So if we want to only filter out the data which contain date range from 00/00/0000 to 00/00/0000.

[@shareyourknowledge6047]

I am looking for the same

[@johnborg6005]

Learning. :) :) thanks to you and yr channel.

[@excelisfun]

You are welcome for the learning, John Borg!!!

[@sallyg5hlee636]

Awesome and very useful stuff going on in this video. Thank you!

[@excelisfun]

You are welcome for the useful, Sally Lee : )

[@ziggle314]

Hi Mike, In your FinalReport query, you actually did not use the AndCriteria variables - other than to assign values to them. I was able to remove top two assignment lines from your M-code, and the Final Report query code still ran correctly. All you needed to use were the query names, ProductCriteria and RegionCriteria directly. Am I thinking about this right?

[@excelisfun]

Where I used the variables in the M Code (step #"Filtered Rows") was an AND Logical Test. Yes, you can just use the variable names without the top two lines of code. I usually try to list everything carefully so it is easy to see and understand. Yes, you are thinking correctly.

[@carlocalingasan998]

You don't need the 2 variable lines in the FinalReport MCode, right? I removed it and the query is still working

[@ikar2k]

Thanks Mike! It's realy comprehensive trio about advantages of using PQ! PS:Using icon of column to choose type in PQ is cool ;)

[@excelisfun]

Glad you like the video!

[@UsmcDuff]

This is a great tip! Is it possible to use this same method to put a variable in the source? I have an API where the product number is what is queried at the very end of the source. If I use the api with the number typed in it pulls the information no problem, but I would like to make that a variable so I could put it in a cell and it would update the query?

[@billythequick]

Great tutorial thanks. I need to be able to return records using wildcards (like *Bike*) rather than an exact match. Can you help?

[@kjafar0502]

Hi Chris, I have the same need. Were you able to solve this and if so can you share how you implemented wildcards in your query?

[@patricknyamu6110]

Thanks But you don't need to declare variables as queries can references other queries ProductCriteria would work just as well

[@excelisfun]

True! : ) But declaring variables can help is two ways: add a clear step that is easily audited, and it can help prevent some errors like Formula.Firewall.

[@AhmedAbdalalim]

Amazing

[@sokgat7019]

Fantastic job!!! Thank you very much!!!

[@excelisfun]

You are welcome!

[@amazingbandproyects3253]

Is it possible, use two criteria for the query? I mean, select USA and Canada on the region.

[@tjmxjulio]

Hi, Thank very much for .this video. I have few time that started using power query. I used your example to query data, however, I would like to know how can i include a option to include all the rows. for example select: A or B or C or All. Once again. Muchas gracias

[@DrK99999]

Can we use list values i.e multiple values in the singke parameter value...e.g. in the example more than 1 region

[@jpaybstudio]

do you have samples for multiple input variable for single column/ field? like a multiple select filter.

[@garethmorrall1047]

Completley new to Power Query. So could that be achieved by just having orig query producing intial table and then table slicers for product and region? Or is it always more efficient to have criteria as power query parameters

[@jazzista1967]

Mike: What a great example. Quick Question: How would you add a total row for the net revenue? .Thanks

[@excelisfun]

You can add a Total Row to the Power Query Excel Table Output. Then when you change records and update, it all works.

[@kamranb1369]

Awesome video Mike, Thanks

[@excelisfun]

You are welcome, K B!!!! Yes, it is amazing how many cool things Power Query can do : )

[@sigogglin]

Can you do multi select? Does M code have an “is in” equivalent like SQL? If I want two regions?

[@Sivon87]

hi, why don't u try this video? It really helps me because I had a problem with filter multi items for pivot table extracted data from power query. kzbin.info/www/bejne/rIe0YWuum7mjars

[@drucregar5631]

Thanks for another great video, two questions on this one. 1. I noticed you passed the query name directly into the filter. Is there a reason you declared the variables in M Code? I removed and it still worked in my test. 2. Can you declare a variable in M Code from a cell value directly or is using another query as the parameter a requirement for some reason?

[@miloradowicz]

1. Yes, he did that. He created variables that use queries to get the criteria data, and then used the same queries again to get the criteria in the filter step. He never ended up using those variables. I don't know how well the M code is optimized, but I'd be worrying about the performance blow of executing a query twice when you don't even use the result of the first call. 2. You can if you put the criteria data gathering code in your filtering query. I don't know if it's a good practice or not though. As a programmer, I would turn the filtering query into a filtering function, and then call it from another query where the source table would be defined.

[@MuhammetMustafaAslan]

Very good one, thanks.

[@excelisfun]

You are welcome! Thanks for the Thumbs Up!

[@abijahjumani9893]

Awesome, really helpful!

[@alrightmundehogona]

Thanks for the video, very helpful

[@excelisfun]

You are welcome for the video, Punith!!! Thank you very much for your support with your comment, thumbs up and Sub : )

[@smplfy]

is there a written version of this? the video has to be stopped too often to verify that no steps were missed. this takes far longer than it should.

[@ForgottenBeastMetal]

Hello that was a great video! Do you have any videos or solutions if parameters are a list and not just single value? I want my users to be able to update the parameter list themselves. I am trying to return true / false if a field contains any of the values in the parameter list. Thank you

[@Mochiandginger]

Do you have a video to list all the files in folder? Then the source folder can be change when new folder location is paste in a certain cell?

[@gbronca]

Great video Mike, exactly what I needed. Congratulations for your great work. Just one question, how would you do if instead of querying one region only you need to query 3 or 4 regions at a time?

[@excelisfun]

Just do an OR Logical Test Filter (check arrows), then set up the variable at the top with 3 or 4 variables separated by commas: (Variable1 As DataType1, Variable2 As DataType2, Variable3 As DataType3)=>

[@rakshitharamesh3161]

​@@excelisfun Can you please elaborate as I am looking for the same? Were to place this logical OR formula

[@pascaljoly5752]

I didn't have to add the 2 lines just under "let" in advanced editor. i still referred to these items and it still worked: let Source = Excel.CurrentWorkbook(){[Name="fSales"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}, {"Region", type text}, {"Units", Int64.Type}, {"Category", type text}, {"Discount", type number}, {"Price", type number}}), #"Net Revenue" = Table.AddColumn( #"Changed Type", "Net Revenue", each Number.Round([Units] * (1-[Discount]) * [Price],2)), Custom1 = Table.RemoveColumns( #"Net Revenue",{"Price","Discount","Units"}), #"Filtered Rows" = Table.SelectRows(Custom1, each ([Product] = ProductCriteria) and ([Region] = RegionCriteria)) in #"Filtered Rows" I also used a different method to change the product and region to text, i used Table.column (Source, "Region"){0} but the result is the same. this is what's great about PQ, you can get to the same result using a different method thanks for all your videos, I am still fairlynew at PQ but i am learning a ton with you! much appreciated

[@KS-xb1xr]

Now what if i have to change something in the actual table and get the updated record later. Can we make a table behind the scene to pull records as conditioned?? A Table within excel.

[@DeenQuery]

Brilliant 😍

[@excelisfun]

Glad it is brilliant for you, Nima!!!!! Thanks for your support on each video with your comments, thumbs ups and of course your Sub : )

[@harshaladia3641]

Thank you so much! Was looking for it since a long time.

[@excelisfun]

You are welcome!

[@DougHExcel]

awesome! and without "writing" VBA too :-)

[@excelisfun]

Glad you like it : )

[@davidferrick]

Great example. I am using it for selecting MONTHS but then realized I needed an option for pulling YTD. Any chance someone can help me find a way to create an option in my droplist to work with a YTD option?