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.
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.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |