Forum Discussion
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
22 5 9
13 22 4
5 6 22
The result table looks like this:
No4 No5 No6 No9 No13 No10 No22
1 2 1 1 1 0 3
I can do the task by creating new column in the new table with IF statement for each distinct value in the source table.
Example of the value 22 occurrneces:
No22 = IF(RELATED(Table1[Column1)=22 || RELATED(Table1[Column2)=22 || RELATED(Table1[Column3)=22,1,0 )
The problem is that i have about 50 columns in the source table and about 30 distinct values, so it will be to long to do that this way.
Is there a shorter way?
(In Excel it would be very simple with COUNTIF)
Thanks
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"
3 Replies
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
