Use current row value to perform calculations on table with matching column name in Power BI?

Question

I have two tables that I am working with. One is a large dataset with about 50 columns. The second table is the Table.Schema() of the first dataset.

I am trying to build a data quality report similar to Table.Profile() but with far more customization. However I am unable to even recreate the base elements of Table.Profile such as Counta or Count of null for each source column.

How I can use the Schema Name values to perform the Count calculation on the Data Source column that the Schema Name represents? See Schema table below for desired output, counting the number of nonblank values in the data source.

NOTE: due to the large number of columns I am looking to build this formula so it is dynamic based off of the name value rather than hardcoding the 50+ column names into formulas

Data Source

ID | Status | Created | Modified
1  | Active | 1/1/19  | null
2  | null   | 1/5/15  | 1/6/15
3  | Active | null    | null

Schema

Name     | Type   | Counta 
ID       | Number | 3
Status   | Text   | 2
Created  | Date   | 2
Modified | Date   | 1

DAX does not have facilities to do the sort of thing you'd like. In Power Query/M, you can reference by name with functions like Table.SelectColumns(table, {"columnname1", "columnname2"}) or Record.Field(record, "fieldname"). As it stands though, your question is far too general to answer effectively. Could you specify more detail about the query you've built and a specific function you'd like to implement? — greggyb
@greggyb is right. You can do some of this sort of thing in M but DAX won't do it at all. — Alexis Olson
@greggyb I updated the question to be more specific. M is fine to use. There are a lot of custom functions I am looking to make and really I am just trying to understand the foundational aspect of how to properly create the reference, but for simplicity I chose Counta. — Moses

greggyb greggyb · Accepted Answer · 2019-08-23T18:54:18

I'll give examples with count, nonnull count, and distinct count, so you can see a couple instances of the pattern.

I'll assume your table data is in a table named Table. Below is the full advanced editor view for adding the counts I mentioned above.

let
     srcTable = Table // This is an arg to all steps below, so useful to have here
    ,schema = Table.Schema(srcTable) // you've used this
    ,count =
        Table.AddColumn( // self-descriptive
             schema      // the source table to add this new column to
            ,"Count"     // the name of the new column we are adding
            // below is a function to evaluate per row in the source table
            // The way to reference a table column by name is
            // Table.Column(table, "column"). That returns a list.
            ,each List.Count(Table.Column(srcTable, _[Name])))
    ,nonNullCount =
        Table.AddColumn(
             count
             ,"Non-null count"
             ,each List.NonNullCount(Table.Column(srcTable, _[Name])))
    ,distinctCount =
        Table.AddColumn(
             nonNullCount
             ,"Distinct Count"
             ,each List.Count(List.Distinct(Table.Column(srcTable, _[Name]))))
in
    distinctCount

Realistically, I would not try to re-implement Table.Profile. I would join the results of Table.Schema and Table.Profile and then add additional profiling to that.

let
     srcTable = Opportunity // This is an arg to all steps below, so useful to have here
    ,schema = Table.Schema(srcTable) // you've used this
    ,profile = Table.Profile(srcTable)
    ,schemaAndProfile =
        Table.NestedJoin(
             schema
            ,"Name"
            ,profile
            ,"Column"
            ,"profile"
        )
    ,expandProfile =
        Table.ExpandTableColumn(
             schemaAndProfile
            ,"profile"
            ,{"Min", "Max", "Average", "StandardDeviation", "Count", "NullCount", "DistinctCount"}
            ,{"profile.Min", "profile.Max", "profile.Average", "profile.StandardDeviation", "profile.Count", "profile.NullCount", "profile.DistinctCount"})
in
    expandProfile

Referencing the columns remains the same as the examples above, but this starts you off at a more robust state.

Use current row value to perform calculations on table with matching column name in Power BI?

1 Answers