Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am trying to create a table from all distinct values from all columns and their count. Any help?
Solved! Go to Solution.
Hi @bshawbulldog,
On the query editor select all your columns, then go to Transform - Unpivot
You will get a table with two columns Attribute e values.
Then on the PBI front do a new table with
Table = SUMMARIZE(Table; Table[Attribute];Table[Value]; "count" ; COUNT(Table[Value]))
This will give you distinct for attributes (column name) and Values (data in table) and count of those values
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @bshawbulldog,
Current I haven't found any functions suitable your requirement, maybe you can use below formula to manually summary values:
Test = VAR temp = UNION ( CROSSJOIN ( ADDCOLUMNS ( { "Column1" }, "Count", COUNTROWS ( VALUES ( Table[Column1] ) ) ), VALUES ( Table[Column1] ) ), CROSSJOIN ( ADDCOLUMNS ( { "Column2" }, "Count", COUNTROWS ( VALUES ( Table[Column2] ) ) ), VALUES ( Table[Column2] ) ), CROSSJOIN ( ADDCOLUMNS ( { "Column3" }, "Count", COUNTROWS ( VALUES ( Table[Column3] ) ) ), VALUES ( Table[Column3] ) ) ) RETURN SELECTCOLUMNS ( temp, "Column Name", [Value], "Detail", [Column1], "Count", [Count] )
Regards,
Xiaoxin Sheng
Hi @bshawbulldog,
On the query editor select all your columns, then go to Transform - Unpivot
You will get a table with two columns Attribute e values.
Then on the PBI front do a new table with
Table = SUMMARIZE(Table; Table[Attribute];Table[Value]; "count" ; COUNT(Table[Value]))
This will give you distinct for attributes (column name) and Values (data in table) and count of those values
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @bshawbulldog,
create the table with the following formula:
SummaryTable = SUMMARIZE(ALL(Table[Column]); Table[Column]; "count" ; COUNT(Table[Column]))
Should give the expected result.
Regards,
Mfelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
That does not work the way I need it to. I'm looking to have all the distinct values from column 1 ... n in a single column.
hi, @bshawbulldog
Your request for help was very general, give us more details or make the database available to help you.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
60 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
104 | |
78 | |
71 |