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"
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