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
Anonymous
Not applicable

Powerquery to create a new column with the following features

Hi All, I was hoping someone could help with this column I am trying to create using power query

 

I have the following table:

 

ClassificationCompanyValue(column needed)
EApple1060
ETesla1555
EApple5060
ETesla4055
SApple2030
SApple1030
STesla55

 

Basically I nede a column (on power query) that does the sum of the value for each classification and company and repeats these values in the cells wherever the company name comes up.

 

For example Apple has to E's, valued 10 and 50. so I want my column to say 60 for both these fields.

 

The DAX code for this is as follows: Column = CALCULATE(sum(Table1[Value]),ALLEXCEPT(Table1,Table1[Classification],Table1[Company]))


Looking for powerquery code so that I can unpivot this column

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Hi @Anonymous 

Paste the following code in a blank (empty) query so that you can see the steps. The relevant one is the last one (#"Added Custom")

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclXSUXIsKMhJBdKGBkqxOhChkNTinESQkClcCKbKFFOVCUQoGEmVEaaQIUIIphFoeiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Classification = _t, Company = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "New column", each List.Sum(Table.SelectRows(#"Changed Type", (inner)=> inner[Classification] = [Classification] and inner[Company]=[Company])[Value]))
in
    #"Added Custom"

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

1 REPLY 1
AlB
Community Champion
Community Champion

Hi @Anonymous 

Paste the following code in a blank (empty) query so that you can see the steps. The relevant one is the last one (#"Added Custom")

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclXSUXIsKMhJBdKGBkqxOhChkNTinESQkClcCKbKFFOVCUQoGEmVEaaQIUIIphFoeiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Classification = _t, Company = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "New column", each List.Sum(Table.SelectRows(#"Changed Type", (inner)=> inner[Classification] = [Classification] and inner[Company]=[Company])[Value]))
in
    #"Added Custom"

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.