Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
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
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)}
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"
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 🙂