Practical use case for Power Query metadata

[@grahamc5531]

I have used something similar, but create the table summary with step counts in the original query, does require some extra manual typing to create the SummaryTable though. And of course, can use to to just reference different steps in the main query and just use that step name in the meta data record, say of you wanted to perform a different filter after the first filter //FilteredData let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"FilterAB-" = Table.SelectRows(Source, each ([BloodType] = "AB-")), FilterLess170 = Table.SelectRows(#"FilterAB-", each [Centimeters] < 170), FilterWA = Table.SelectRows(FilterLess170, each ([State] = "WA")), SummaryTable = #table( type table [ Step Name = text, Step Count = Int64.Type ], { {"Filter AB-", Table.RowCount(#"FilterAB-")}, {"Filter Less Than 170", Table.RowCount(FilterLess170)}, {"Filter WA", Table.RowCount(FilterWA)} } ) in FilterWA meta [SummaryTable = SummaryTable] and then the other query would be a simple one line //FilteredCount let Source = Value.MetaData(FilteredDate)[SummaryTable] in Source

[@AccessAnalytic]

Ah yeah, nice approach

[@AccessAnalytic]

I also like this solution suggest by Elliot Paterson on LinkedIn let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"FilterAB-" = Table.SelectRows(Source, each ([BloodType] = "AB-")), FilterLess170 = Table.SelectRows(#"FilterAB-", each [Centimeters] < 170), FilterWA = Table.SelectRows(FilterLess170, each ([State] = "WA")), CountTable = [ F0 =Source, F1= #"FilterAB-", F2= FilterLess170, F3 = FilterWA ], FinalTable = FilterWA meta [metaSummary = CountTable] in FinalTable

[@vinhwizard]

Hay quá, tôi chưa hiểu về Meta, nhưng thấy thầy làm tuyệt vời

[@donmcgimpsey1706]

I really like this solution - I will be looking for a problem to apply it to. Love it - a solution looking for a problem. I do have a related question - about filtering. Can I setup a SQL statement, that is ready to execute, and pass it a parameter, such as an Order# or such - and execute the query dynamically, and update the table. The use case would be to have reports setup that the user can select order# and retrieve the data for just those rows. (could be Oracle or SQL Server) - thanks, I am rewatching many of your videos!

[@AccessAnalytic]

I think you want something like this pawarbi.github.io/blog/power%20bi/powerquery/queryfolding/m/optimization/2022/01/25/parameter-valuenativequery-query-folding-where-clause-in-powerbi.html As mentioned at the start of the article, watch Adam’s video first

[@matthewpage4706]

Didn't know about the meta info - thanks. I can see how I might use that in future. With this one I happened to be trying to do something similar this week & inserted and extra stage into the code: = #table({"Stage", "Count"}, { {"FilterAB",Table.RowCount(#"FilterAB")}, {"FilterLess170",Table.RowCount(#"FilterLess170")}, {"FilterWA",Table.RowCount(#"FilterWA")} }) Obviously it's a bit manual so gets harder the more times you filter, but it's pretty concise overall.

[@matthewpage4706]

Also didn't know about the shift shuffle at 4:08 so thanks.

[@AccessAnalytic]

You’re welcome 😀

[@terrylee6904]

Thanks for Sharing!

[@AccessAnalytic]

You're welcome

[@sailedship6530]

Thank you for share I used to calculate these as a measure with Calculate(CountRows) before hitting "Close & Apply" (it was a hack while in temp memory). I don't remember exactly, it's been more than 3yrs. Do you know of any dedicated forum you recommend for such questions and answers?

[@AccessAnalytic]

Interesting - I didn’t know that was possible. www.reddit.com/r/PowerBI/ community.powerbi.com/t5/Desktop/bd-p/power-bi-designer

[@mnowako]

Awesome! Thank you!

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@nevermore17011990]

tks for all videos, but can you make video of cleaning data in real case, from begin to end. ex : we crawl data from a web or sth like that, the rawdata will be totally mess.

[@AccessAnalytic]

kzbin.info/www/bejne/bmndfIyYr690q7Msi=XasJ9TbY3tizzOVe And a playlist kzbin.info/aero/PLlHDyf8d156Vftkr31u-K59Ril6hn3rEY&si=KSzkt9SKqaPIKmeJ

[@JenMayB]

Could i also reference the [table]{step} in a new query around the count function? Maybe do an add column for each step to count? I need to see if that would work.

[@AccessAnalytic]

Check out the pinned comment for a couple of approaches

[@CaribouDataScience]

That is pretty spiffy

[@AccessAnalytic]

I agree :)

[@Simon-vc1wk]

It's the reason why pq sits there evaluating

[@Simon-vc1wk]

Could you not achieve the same with group by and count records

[@AccessAnalytic]

Sort of, but I needed the original output to still load, and wanted a summary output of number of items filtered so groupby wouldn’t have been ideal for that

[@davebickers7209]

Could you duplicate your original query and have one "commented" and the 2nd "uncommented"?

[@AccessAnalytic]

Yep definitely an option, but I’d prefer to avoid maintaining 2 queries

[@shefaliaggarwal7201]

Hlo sir I have a problem in vba code in excel Please help me sir

[@AccessAnalytic]

Hi, I would post your issue to www.reddit.com/r/excel/

[@pon00050]

Full disclosure that I asked ChatGPT to write the following. My idea is, create a custom function that calculates and outputs the number of existing records for each step thus far. // Custom function to count rows for all steps (querySteps as list) as table => let // Generate a list of records where each record contains the step name and row count RowCounts = List.Transform(querySteps, each [StepName = _[StepName], RowCount = Table.RowCount(_[Table])]), // Convert the list of records to a table ResultTable = Table.FromRecords(RowCounts) in ResultTable let // Original data source Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // Define each step FilterAB = Table.SelectRows(Source, each ([BloodType] = "AB-")), FilterLess170 = Table.SelectRows(FilterAB, each [Centimeters] < 170), FilterWA = Table.SelectRows(FilterLess170, each [State] = "WA"), // Create a list of steps with their respective names MyQuerySteps = { [StepName = "Source", Table = Source], [StepName = "FilterAB", Table = FilterAB], [StepName = "FilterLess170", Table = FilterLess170], [StepName = "FilterWA", Table = FilterWA] }, // Call the custom function to get the row count for each step RowCountsTable = RowCountFunction(MyQuerySteps) in RowCountsTable

[@AccessAnalytic]

Thanks, I like the approach in the pinned comment ( see the comment I make about the code from Elliot )

[@maayanpoleg4278]

I suggest a less elegant way, but more understandable to the user , with minimal use of M: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], FilterAB = Table.SelectRows(Source, each ([BloodType] = "AB-")), Count1 = List.NonNullCount(FilterAB[Number]), BackToTbl1 = FilterAB, FilterLess170 = Table.SelectRows(BackToTbl1, each [Centimeters] < 170), Count2 = List.NonNullCount(FilterLess170[Number]), BackToTbl2 = FilterLess170, FilterWA = Table.SelectRows(BackToTbl2, each ([State] = "WA")), Count3 = List.NonNullCount(FilterWA[Number]), BackToTbl3 = FilterWA, FilteredList = {Count1, Count2, Count3} in FilteredList

[@AccessAnalytic]

Ah - I should have said I still needed the original table to load to the file. With this approach you could do the summarisation piece at the end AND importantly identify which numbers relate to which filter by doing something like: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"FilterAB-" = Table.SelectRows(Source, each ([BloodType] = "AB-")), FilterLess170 = Table.SelectRows(#"FilterAB-", each [Centimeters] < 170), FilterWA = Table.SelectRows(FilterLess170, each ([State] = "WA")), Counts =Table.FromRows( { {"Source",Table.RowCount(Source)}, {"FilterA", Table.RowCount( #"FilterAB-")}, {"C", Table.RowCount(FilterLess170)}, {"D", Table.RowCount(FilterWA)} },{"Step","Count"} ) in Counts

[@ryanyou1583]

Maybe no need to use the Meta function, simply convert the entire query to a RECORD, then we could convert it back to table, then do the countrows. here is the code: let testing = [ Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"FilterAB-" = Table.SelectRows(Source, each ([BloodType] = "AB-")), FilterLess170 = Table.SelectRows(#"FilterAB-", each [Centimeters] < 170), FilterWA = Table.SelectRows(FilterLess170, each ([State] = "WA")) ], #"Converted to Table" = Record.ToTable(testing), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.RowCount([Value])) in #"Added Custom"

[@AccessAnalytic]

Check out the pinned comment for a couple of approaches . I need to load the main table as well as query it