Create a column from all distinct values from all columns

[bshawbulldog]

I am trying to create a table from all distinct values from all columns and their count. Any help?

[v-shex-msft]

Hi @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

[bshawbulldog]

Hi @v-shex-msft

The problem here is I might have 100 different columns.

[MFelix · Accepted 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

[MFelix]

Hi  @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

[bshawbulldog]

Hi @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.

[Rfranca]

hi, @bshawbulldog

Your request for help was very general, give us more details or make the database available to help you.