Multiple columns calculation

[dpbi]

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

[MarcelBeug · Accepted solution]

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"

[riyuk11]

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

[dpbi]

Thank you very much for the fast respone.

It works perfect.

Thanks again.