Manipulate Profile function to filter data

Power Query
1

Hello,

Is it possible to use the Table.Profile() function to perform analysis on the data (min, max, null counts, etc) along with the ability to view the details (min, max, null counts, etc) through a filter at the row level by a grouping?

Sample Data:

image
image829×213 5.06 KB

After using Table.Profile function:

image
image1133×138 6.24 KB

However, I wish to view the null count by Location and not just on the overall dataset/column level.
Desired output: (This is only for null count, but basically for the entire Table.Profile() output):
image

Also, if there are other ways to perform this sort of profiling without using Table.Profile() that doesn’t require setting up calculations for every location and column, I would be very interested to know!

DemoDataProfile - Copy.pbix (35.0 KB)

Thanks a lot.

2

Hi @jay89,

Give this a go. Just paste this code into a new blank query in your file.

let
    Source = Data,
    GroupRows = Table.Group(Source, {"Location"}, {{"AllRows", each Table.Profile( _ ) }}),
    ExpandProfile = Table.ExpandTableColumn( GroupRows, "AllRows", Table.ColumnNames( GroupRows[AllRows]{0} ), Table.ColumnNames( GroupRows[AllRows]{0} ) )
in
    ExpandProfile

.
Returns this result.

image
image1478×453 14.4 KB

I hope this is helpful.

3

Thank you so much for that detailed answer @Melissa

We hope this helped you @jay89 :slight_smile:

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

4

Hello @jay89, it’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Thank you, Melissa! This worked perfectly.

1 Like