Forum Discussion

bshawbulldog's avatar
bshawbulldog
New Member
8 years ago
Solved

Create a column from all distinct values from all columns

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

  • MFelix's avatar
    MFelix
    8 years ago

    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

     

     

6 Replies

  • Rfranca's avatar
    Rfranca
    Icon for Resolver IV rankResolver IV
    8 years ago

     

    hi, bshawbulldog

     

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

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      bshawbulldog
      New Member
      8 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      bshawbulldog
      New Member
      8 years ago

      Hi Anonymous

      The problem here is I might have 100 different columns.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        8 years ago

        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