Practical use case for Power Query metadata

How to extract the number of records at each filter step in a query.
Thanks to these 2 resources:
🧑🏻‍💻Chandeep:
• Reference an Intermedi...
🧑🏻‍💻Abhay Gadiya
• Referencing Intermedia...
🔢Code:
Code also inside the Excel file
let
//MyQuerySteps = [
Your Code
//] in MyQuerySteps[YourLastStep] meta [MetaReference = MyQuerySteps]
in YourLastStep
📂Copy of File
aasolutions.sh...
Access Analytic
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic...
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

[@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

[@terrylee6904]

Thanks for Sharing!

[@AccessAnalytic]

You're welcome

[@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 😀

[@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]

ua-cam.com/video/88zKYbzJAuQ/v-deo.htmlsi=XasJ9TbY3tizzOVe
And a playlist
ua-cam.com/play/PLlHDyf8d156Vftkr31u-K59Ril6hn3rEY.html&si=KSzkt9SKqaPIKmeJ

[@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

[@CaribouDataScience]

That is pretty spiffy

[@AccessAnalytic]

I agree :)

[@Simon-vc1wk]

It's the reason why pq sits there evaluating

[@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

[@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

[@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

[@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/

[@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