Glad you can learn a lot from my videos, prabhu!!!! Thanks for your support on each video that you learn from with those comments, thumbs ups, and of course your sub : )
Hello Vida, Just want to know if the reference Cell is Calendar or you just type the date in that field .... @Mike you are also welcome for the comment.
Thanks for the video. Interesting to see that you only have to refresh the fTable Query to see the effect. BTW: you could have done the same by right-clicking on the resulting table and selecting refresh, just like with a pivot table.
Okay! I'll make more, as long as you keep supporting with your comments and thumbs ups!!! How about a new video today on this same topic, but bringing the variable into a DAX Formula?
Thanks Mike For Finding the M code of advanced Filter In Excel:-) a lot of funnn and a lot of practise :-) The same result could be reached by using advanced Filter
Yes, it could. But if data is coming from external source, then Power Query is probably easier than Advanced Filter. But this method of getting a variable into Power Query could be used in any situation, data filtering or not : )
Mike, great video. I much prefer using named ranges than tables which work in the same way. More often than not, my filters are vertical on the spreadsheet or in non-adjacent cells, so I find using the cell names nicer. Same principal of course.
No, no, no, Advanced Filter still has so great uses : ) With Advanced Filter: 1) We don't have to convert to Excel Tables, 2) We don't get the yellow button at the top of the worksheet when we open the Workbook, 3) Tasks like Comparing two lists in the Excel Sheet is easier, 4) We do not want output as an Excel Table... and more. But yes, somethings are easier with Power Query : ) The grip is loosening.
So is there an easy way to use the Data List drop down to refresh on opening to show all the sheet names and dates, so you can then select say an updated price list? Or tell Power Query to only use the latest updated xlsx file in a folder? I ask this because we don’t want to delete to old file in case we want to reference that in a later query?
Hi Mike, thank you this. How could I adapt the Table.SelectRows to ignore the Website argument if it is left blank on purpose to only perform the filtering by the product argument instead?
Is there a way to do this with a Date? When I try to enter it in, it wants multiple inputs for month, day, year, and I'd rather create a variable based on the Today() function Excel.
wow! it makes a power dynamic query, so we don't need to go to power query to edit parameters every time, right? and I have one question, is it able to make query result refresh automatically?
No. THE only feature in all of Excel that can do that is formulas. That's it. Think about it: We do not mind if we have to refresh our PivotTable or Advanced Filter result when source data changes, so we should not mind with Power Query either. Said a different way: The beauty of formulas for the 40 year history of spreadsheets is that they update when source data changes, everything else needs a refresh. However, you could use VBA to recognize when the cell has changed to do an automatic update.
@@excelisfun Thanks teacher Mike, yep , Thinking about PviotTable and Advanced Filter and power query , they really similar, we have to refresh the result wheen source data changes. thanks to teacher Mike , you expand my knowledge about Excel more deeply again!
Mike... I'm attempting to refer to a worksheet cell for writing a function when adding a column in Power Query. I feel like this should be common, but I can't find any information on the subject. Specifically, I am projecting overtime on our fire department's schedule. I have a query that shows the number of firefighters working by date and a cell in the workbook that contains the minimum manning number. How do I "import" the minimum manning number into a function within the query? Any help would be great. If you have a video that shows this, please share! Thank you very much in advance.
Hello, i come across a peculiar problem while connecting a file to power query. The file downloaded as html with.. xls extension. Pl give a solution at the earliest.
How would you make this work with a list? (not a drop down selection but if I type {1,5,8} I want to use this as a list for selection. (i tried some list.contains, but that was ofc wrong since my variable is not a list but a variable... -.- - I could ofc make a list next to the table i use as a variables - but this is not as elegant as if i could make this work
MG - thanks for the informative video. Just trying to think, when should I use this setup? If my fact table has +1 million rows of data and I want to query it based on a drop-down list?
Yes, this is a good use, but better to use Data Model and DAX VALUES function as seen in next EMT: th-cam.com/video/nVoqWGIN7Mc/w-d-xo.html If my fact table has +1 million rows of data and I want to query it based on a drop-down list? In MSPTDA video I show how to import from 7 million rows in Data Model into Excel Sheet: th-cam.com/video/Rbkbr89cuHo/w-d-xo.html at the 02:03:15 hour mark.
Some reason I am not able to make this work.. I created a variable but when i apply it to main query filter condition nothing get pulled up. After adding a filtering condition in main query i get a security message as 'Permission is required to run this native database query'. No matter what i select here, i get empty result after applying the variables.. I am retrieving data from ODBC connection.. When i apply my filter reference 'Variable' it is not returning any result. Table.SelectRows(Source, each ([TRID] = "Variable"))
@@excelisfun thank you for the help. Hope Microsoft finds a way to solve this.. as in my work when i talk about Power query people still think it's sorcery.
@@VITORB82 Well. Power Query is the greatest Excel Invention since the PivotTable, and it took about 10 years until everyone was using PivotTables. It takes time. But in terms of refreshing, remember things in Excel like PivotTables, Advanced Filter and other tools, they all require a refresh!!!!!!!!! So it should not be a big deal. Formulas are THE only feature in Excel that refreshes automatically when source data changes. Thanks for stopping by in the comments, Vitor.
hey good job ,, here to resolve one of my querry . got stuck on one of my project .. (eg.i get daily attendence sheet of 30 student and i have to sort and record yearly 5 students attendance data by days , so i used INDEX AND MATCH function to match current date and student name to give present days attendence and record it , but the problem thats coming is as excel doesn't have ENDIF function so as a result each present day the sheet is updating and couldn't aable to record previous days data ,, please guide through me
wow - Nice (I was congratulating my self for thinking this was possible, now im thinking you for showing me how:) ) - one thought though - I would like to make this a bit more flexible so i have 5 parameters like (customers, order, date_from, Date_to and location) - but what if i want to be able to select only say 1 or 3 of them and not all 5 (Eg. i select dates +customer ) or i select only location then rest should be blank (all) - can you do that ? (im thinking you can... will you show me ? )
ok so this work where "Ændret type" is the step before this = if #"Variable_ ordre" =null then (Table.SelectRows(#"Ændret type", each ([WS.VE_OR_ID] null))) else (Table.SelectRows(#"Ændret type", each ([WS.VE_OR_ID] =#"Variable_ ordre")))
If your Variable is a Folder or File Path, then you need to use this technique: th-cam.com/video/0NX-GctfZuU/w-d-xo.html
Used this at work today to avoid hard-coding a date limiter. Thanks!
I feel like a boomerang, I go away and keep coming back for more!
Love it!!!! Love the boomerang simile and that you keep coming back for more fun With Excel : )
Djl, what are you talking about... I never leave! ;-)
@@GeertDelmulle Nice!!!!!!!!!!!
Same for me :)
Very ez to understand. Thank you.
this is what i've been looking for years!!!!!
You are a great teacher Sir!! I learned a lot from you. Thank you very much
Glad you can learn a lot from my videos, prabhu!!!! Thanks for your support on each video that you learn from with those comments, thumbs ups, and of course your sub : )
Thank you so much Mike, helped me a lot. Excellent explanation
You are welcome, Vivek!!!
Hi Mike.. super cool tip to bring a sheet variable into Power Query. Thanks for the technique and thorough explanation. Thumbs up!
You are welcome, Wayne! Thanks for the support : )
Thank you Mike. This helped me solve an urgent problem today and learned something new along the way. Magic Tricks are awesome!
As always, very informative and helpful, Thank you very much.
Glad it is helpful, Linda!!!!
This was very helpful. Thanks Mike!
You are welcome, Teammate Kevin!!!!
Thanks, Mike, I used this trick recently in one of my client's reports!
Lucky client to have you ; )
Good one!
Thanks, Oz!!!!
Thanks for demo-ing the Positional and Field Access Operators!
Clear, simple and handful 🎉🎉🎉
Glad you like it!!!
Nice one. Big thanks!
You are Big welcome!!!
This was great, thanks Mike!
You are welcome, Chris!
I love this way of working Mike. I use a similar method for getting lists of filenames from multiple SharePoint libraries...so much quicker 😉
Cool example. So, in your example, you provide Power Query with List Name from Excel Sheet?
thank you was stuck with my variables, now it s fine thank you
Clear and precise. Thank you.
You are welcome!
Absolute legend. Thank you
You are welcome!!!
Great way of filtering table. I used this method with dates to extract data for a certain period
Cool, Vida!!!!
Hello Vida, Just want to know if the reference Cell is Calendar or you just type the date in that field .... @Mike you are also welcome for the comment.
Thanks for the video. Interesting to see that you only have to refresh the fTable Query to see the effect.
BTW: you could have done the same by right-clicking on the resulting table and selecting refresh, just like with a pivot table.
Good tip: just right-click refresh the table : ) P.S. Thanks for the idea, Geert : ) : )
Mike, you’re welcome. Take good care of yourself!
Like the way you use the positional index operator and filed access operator to get the value instead of using the drill down:):)
Great Mike, thank you
You are welcome, Katerina : )
Thank you Mike, please make more videos for us, you are amazing!
Okay! I'll make more, as long as you keep supporting with your comments and thumbs ups!!! How about a new video today on this same topic, but bringing the variable into a DAX Formula?
Amazing!
Glad it is amazing for you, Conrado!!!!
Thanks Mike For Finding the M code of advanced Filter In Excel:-) a lot of funnn and a lot of practise :-)
The same result could be reached by using advanced Filter
Yes, it could. But if data is coming from external source, then Power Query is probably easier than Advanced Filter. But this method of getting a variable into Power Query could be used in any situation, data filtering or not : )
@@excelisfun yes it is :-)
Mike, great video. I much prefer using named ranges than tables which work in the same way. More often than not, my filters are vertical on the spreadsheet or in non-adjacent cells, so I find using the cell names nicer. Same principal of course.
Excel Names are a great tool! Love it that you use them, Rico S : )
Thanks as ever.
You are welcome as ever, Oakley : )
Really useful and powerful, my grip on Advanced Filter is coming loose!!
No, no, no, Advanced Filter still has so great uses : ) With Advanced Filter: 1) We don't have to convert to Excel Tables, 2) We don't get the yellow button at the top of the worksheet when we open the Workbook, 3) Tasks like Comparing two lists in the Excel Sheet is easier, 4) We do not want output as an Excel Table... and more. But yes, somethings are easier with Power Query : ) The grip is loosening.
@@excelisfun Ha Ha, yep, I ain't going to give it up just yet, still my fav!
@@davebowman5392 Me too! We should start the Advanced Filter Fan Club : ) : )
thank you!
You are welcome!
Excellent. Thanks Mike
You are welcome, Matt!!!!
Nice. Thank you!...👍👍👍
Briliant and super‐easy‐cool!
I like this: super-easy-cool : ) : )
So is there an easy way to use the Data List drop down to refresh on opening to show all the sheet names and dates, so you can then select say an updated price list? Or tell Power Query to only use the latest updated xlsx file in a folder?
I ask this because we don’t want to delete to old file in case we want to reference that in a later query?
Hi Mike, thank you this.
How could I adapt the Table.SelectRows to ignore the Website argument if it is left blank on purpose to only perform the filtering by the product argument instead?
Thanks mike. Very interesting.
You are welcome, John Borg! Thanks for always stopping by in the comments : )
@@excelisfun Its's the least anyone can do for all of this. : ) : )
how to do this when cell value B5, for product, would include the option to choose "ALL" (so actually the user then chooses not to filter)?
Is there a way to do this with a Date? When I try to enter it in, it wants multiple inputs for month, day, year, and I'd rather create a variable based on the Today() function Excel.
wow! it makes a power dynamic query, so we don't need to go to power query to edit parameters every time, right? and I have one question, is it able to make query result refresh automatically?
No. THE only feature in all of Excel that can do that is formulas. That's it. Think about it: We do not mind if we have to refresh our PivotTable or Advanced Filter result when source data changes, so we should not mind with Power Query either. Said a different way: The beauty of formulas for the 40 year history of spreadsheets is that they update when source data changes, everything else needs a refresh. However, you could use VBA to recognize when the cell has changed to do an automatic update.
@@excelisfun Thanks teacher Mike, yep , Thinking about PviotTable and Advanced Filter and power query , they really similar, we have to refresh the result wheen source data changes. thanks to teacher Mike , you expand my knowledge about Excel more deeply again!
@@donychen2162 You are welcome, Dony! Thanks for stopping by in the comments : )
Mike... I'm attempting to refer to a worksheet cell for writing a function when adding a column in Power Query. I feel like this should be common, but I can't find any information on the subject. Specifically, I am projecting overtime on our fire department's schedule. I have a query that shows the number of firefighters working by date and a cell in the workbook that contains the minimum manning number. How do I "import" the minimum manning number into a function within the query? Any help would be great. If you have a video that shows this, please share! Thank you very much in advance.
Great solution, Thanks
You are welcome, Ogwal!!
Thanks for all the great videos.
Question: If the data is large does this use of variable slow down the query?
Hello, i come across a peculiar problem while connecting a file to power query. The file downloaded as html with.. xls extension. Pl give a solution at the earliest.
excellent!
Thanks, Steven Nye : )
Cool!
How would you make this work with a list? (not a drop down selection but if I type {1,5,8} I want to use this as a list for selection. (i tried some list.contains, but that was ofc wrong since my variable is not a list but a variable... -.- - I could ofc make a list next to the table i use as a variables - but this is not as elegant as if i could make this work
did you find the solution for this ?
Great
Glad it is great for you, Paul : )
MG - thanks for the informative video. Just trying to think, when should I use this setup? If my fact table has +1 million rows of data and I want to query it based on a drop-down list?
Yes, this is a good use, but better to use Data Model and DAX VALUES function as seen in next EMT: th-cam.com/video/nVoqWGIN7Mc/w-d-xo.html
If my fact table has +1 million rows of data and I want to query it based on a drop-down list? In MSPTDA video I show how to import from 7 million rows in Data Model into Excel Sheet: th-cam.com/video/Rbkbr89cuHo/w-d-xo.html at the 02:03:15 hour mark.
Trying to use a date range from user input and can't figure it out. Any ideas?
Some reason I am not able to make this work.. I created a variable but when i apply it to main query filter condition nothing get pulled up. After adding a filtering condition in main query i get a security message as 'Permission is required to run this native database query'. No matter what i select here, i get empty result after applying the variables.. I am retrieving data from ODBC connection..
When i apply my filter reference 'Variable' it is not returning any result.
Table.SelectRows(Source, each ([TRID] = "Variable"))
Thank You, O Master!!! :-))
You are welcome, O PQ Poet Master!!!!
How we can apply same features on data range?
Any way to update automaticaly the filtered table?
You can do a line or two of VBA. But I do not know a non-VBA way.
@@excelisfun thank you for the help. Hope Microsoft finds a way to solve this.. as in my work when i talk about Power query people still think it's sorcery.
@@VITORB82 Well. Power Query is the greatest Excel Invention since the PivotTable, and it took about 10 years until everyone was using PivotTables. It takes time. But in terms of refreshing, remember things in Excel like PivotTables, Advanced Filter and other tools, they all require a refresh!!!!!!!!! So it should not be a big deal. Formulas are THE only feature in Excel that refreshes automatically when source data changes. Thanks for stopping by in the comments, Vitor.
hey good job ,, here to resolve one of my querry . got stuck on one of my project .. (eg.i get daily attendence sheet of 30 student and i have to sort and record yearly 5 students attendance data by days , so i used INDEX AND MATCH function to match current date and student name to give present days attendence and record it , but the problem thats coming is as excel doesn't have ENDIF function so as a result each present day the sheet is updating and couldn't aable to record previous days data ,, please guide through me
wow - Nice (I was congratulating my self for thinking this was possible, now im thinking you for showing me how:) ) - one thought though - I would like to make this a bit more flexible so i have 5 parameters like (customers, order, date_from, Date_to and location) - but what if i want to be able to select only say 1 or 3 of them and not all 5 (Eg. i select dates +customer ) or i select only location then rest should be blank (all) - can you do that ? (im thinking you can... will you show me ? )
ok so this work where "Ændret type" is the step before this = if #"Variable_ ordre" =null then (Table.SelectRows(#"Ændret type", each ([WS.VE_OR_ID] null))) else (Table.SelectRows(#"Ændret type", each ([WS.VE_OR_ID] =#"Variable_ ordre")))
First! lol
First place trophy goes to.......... brianxyz!!!!!!!