Build a data profile query from list of table names

[cmengel]

Hi!

I'd like to build a query that creates a table based on the Table.Profile function.

I'd like to 'feed' the function a list of table names and then add a custom column to contain the table name itself.

The resulting table enables me to generate some high-level data quality / data integrity visuals in Power BI.

Here's the code I have so far, and I'm a bit stuck:

let
TABLE_NAMES = {"DIM_DATE", "DIM_HEADER", "DIM_WO_GEOGRAPHY_CN", "DIM_EQUIPMENT_CN", "DIM_SEGMENT", "DIM_WORKORDER_EQUIPMENT_CN"},
Source = Table.Profile(TABLE_NAMES),
#"Added Custom" = Table.AddColumn(Source, "TABLE_NAME", each TABLE_NAMES),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Count", Int64.Type}, {"NullCount", Int64.Type}, {"DistinctCount", Int64.Type}})
in
#"Changed Type"

This community is the greatest!

Thanks in advance for your help and suggestions.

Warm regards,

Chris

[mahoneypat · Accepted solution]

Not sure if this is what you need, but you can use this expression to get the profile of all tables in your model.  Note the first filter removes the name you give this query (to avoid a circ reference).  You could further filter it to only include the tables you want.

let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Added Custom1" = Table.AddColumn(#"Converted to Table", "Custom", each Value.Is([Value], type table)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [Name] <> "ThisQuery"),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Custom] = true)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Profile", each Table.Profile([Value])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Custom"}),
    #"Expanded Profile" = Table.ExpandTableColumn(#"Removed Columns", "Profile", {"Column", "Min", "Max", "Average", "StandardDeviation", "Count", "NullCount", "DistinctCount"}, {"Column", "Min", "Max", "Average", "StandardDeviation", "Count", "NullCount", "DistinctCount"})
in
    #"Expanded Profile"

Pat

[Syndicate_Admin]

Hi Pat!

Well, I thought I was in the clear because I didn't get the error message, but now I have a new issue:

The query steps look like they're working, but there's no distribution information.  Additionally, after I hit close and load, I end up with an empty table.

Any thoughts on why that might be the case?

[IB_what]

Me too. any resolusions? 

thanks 

[cmengel]

Hi Pat!

Well, I thought I was in the clear because I didn't get the error message, but now I have a new issue:

The query steps look like they're working, but there's no distribution information.  Additionally, after I hit close and load, I end up with an empty table.

Any thoughts on why that might be the case?

[cmengel]

Hi Pat!

Thanks so much for the prompt reply.

This is a really interesting approach and certainly moves me toward my objective.

When I step through the Applied steps, specifically Filtered Rows to Filtered Rows1, it throws an 'Unexpected Error":

Filtered Rows Step: (all is well)

Filtered Rows1 step: (suddenly not well)

The issue was on my end.  I rearranged a few of the steps to filter right after the table is created and that resolved my issue.  This is an excellent pattern!

Thanks again for the quick response and very slick approach!  Love it!