Forum Discussion
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 ...
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"
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"
Thank you very much for the fast respone.
It works perfect.
Thanks again.
Hi,
i have a diffrent senerio---
i have two following tables..
table 1- usage
1 wk-18 2 wk-18 3 wk-18 4 wk-18 5 wk-18 316.32 381.1 404.45 400.9 367.28 31.79 41 50.75 38.31 42.53 16.88 17.63 14.74 13.91 16.48 table 2- capacity
1 wk-18 2 wk-18 3 wk-18 4 wk-18 5 wk-18 3072 3072 3072 3072 3072 4410 4410 4410 4410 4410 1000 1000 1000 1000 1000 i need a new table (table 3), in which value of table 1devided by value of table two in same table format like this..
1 wk-18 2 wk-18 3 wk-18 4 wk-18 5 wk-18 10.30% 12.41% 13.17% 13.05% 11.96% 0.72% 0.93% 1.15% 0.87% 0.96% 1.69% 1.76% 1.47% 1.39% 1.65% please help me
