Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dpbi
Helper I
Helper I

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

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

Hi,

 

i have a diffrent senerio---

 

i have two following tables..

 

table 1- usage

1 wk-182 wk-183 wk-184 wk-185 wk-18
316.32381.1404.45400.9367.28
31.794150.7538.3142.53
16.8817.6314.7413.9116.48

  

table 2- capacity

1 wk-182 wk-183 wk-184 wk-185 wk-18
30723072307230723072
44104410441044104410
10001000100010001000

 

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-182 wk-183 wk-184 wk-185 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

Thank you very much for the fast respone.

 

It works perfect.

 

Thanks again.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.