Forum Discussion

dpbi's avatar
dpbi
Helper I
8 years ago
Solved

Multiple columns calculation

Hi. I need to count occurrences of distinct values from multiple columns in one table. and show the results in a new table. My source table  (Table1) looks like this: Column1  Column2  Column3   ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    In Power Query you can unpivot your table and pivot back on the values column, like in the query below.

     

    let
        Source = SourceTable,
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
        #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type text}}),
        #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Value", each "No" & _, type text}}),
        #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Value]), "Value", "Attribute", List.Count)
    in
        #"Pivoted Column"
MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

In Power Query you can unpivot your table and pivot back on the values column, like in the query below.

 

let
    Source = SourceTable,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Value", each "No" & _, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Value]), "Value", "Attribute", List.Count)
in
    #"Pivoted Column"
dpbi's avatar
dpbi
Helper I
8 years ago

Thank you very much for the fast respone.

 

It works perfect.

 

Thanks again.