Table.Profile "expanded" statistics in Power M (or DAX)

[gdecome]

In Power Query we have interesting statistics about each column (Column Quality, Column Distribution, Column Profile). From Column Profile we can get some numbers using the function Table.Profile() ... but I don´t get values to build the chart we see in Value Distribution (count of each value in the column).
My intention is to build a view in PBI to give some insights about data quality for the users.
The question is ... how can I get the list and count of values in columns? I could this using some hard code in M but I´d like to make it flexible (may a function) to be used in different tables/columns. In the view, user could select one column, from a slicer, and see statics/charts for that specific column. Not sure if a virtual table in DAX could solve the problem considering the column name is variable. Any help is very welcome.

[v-kelly-msft]

Hi @gdecome，

I guess you can use "distinct" or "values" function to get the list of the column.But both functions will remove duplicate values.

Here is the reference:

https://docs.microsoft.com/en-us/dax/distinct-function-dax

https://docs.microsoft.com/en-us/dax/values-function-dax

As for "count of values in columns",you can use "count","distinctcount"or"countrows" per your request.

Here is the reference:

https://docs.microsoft.com/en-us/dax/count-function-dax

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax

https://docs.microsoft.com/en-us/dax/countrows-function-dax

If above is not what you need,better provide some sample with your expected output,I will advise you how to achieve it.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[gdecome]

Hi @v-kelly-msft , thank you for your suggestion.
In fact, I´m looking for something more "flexible" where I don´t need to hard code the column name.

So far, I´m using the code below that seems to be working in the way I need (still WIP)

let

Profile =
Table.Profile(
     MyTable,
     {  { "Value", each Type.Is(_, type any), List.Sort }  }
)
in
   Profile

With that I have a new column in the table, besides the other statuses, with the list of all values in that column. I can expand this list, count each value and create a chart similar what we see in Power Query screen. I couldn´t find many documentation about the second parameter in Table.Profile but there are some interesting options to improve information. Not sure yet how to use other functions on that when the function requires more than one parameter (e.g List.FirstN). Also, the only List function that I found to return the full list of values was List.Sort. If you have more information or tips on that, please, let me know. Thanks again

[gdecome]

BTW ... I figured out my problem when trying to enter a function with more than one parameter:

Instead of (that returns error)

      {  { "Value", each Type.Is(_, type any), List.FirstN(_,2) }  }

Need to include a extra "each"

     {"Value", each Type.Is(_, type any), each List.FirstN(_,2)}

[v-kelly-msft]

Hi  @gdecome ,

Try below M code:

let
  Profile = Table.AddColumn(Table.Profile(Table),"ColumnValue",each 
    let 
    cname = [Column],
    t = Table.RenameColumns(Table.Distinct(Table.SelectColumns(Table,[Column])),{[Column],"Value"})
    in Table.AddColumn(t, "ValueCount", each let v = [Value] in Table.RowCount(Table.SelectRows(Table.RenameColumns(Table.SelectColumns(Table,cname),{cname,"OriginValue"}),each [OriginValue] = v)))
  ),
    #"Expanded ColumnValue" = Table.ExpandTableColumn(Profile, "ColumnValue", {"Value", "ValueCount"}, {"ColumnValue.Value", "ColumnValue.ValueCount"})
in
    #"Expanded ColumnValue"

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[gdecome]

Excellent ... I got a similar result with some other steps but it is taking some time to process. Your technique is quite interesting and it is working fine. I will do some additional tests to keep the more efficient process in terms of time.
Thank you very much for your help 🙂