Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All, I was hoping someone could help with this column I am trying to create using power query
I have the following table:
Classification | Company | Value | (column needed) |
E | Apple | 10 | 60 |
E | Tesla | 15 | 55 |
E | Apple | 50 | 60 |
E | Tesla | 40 | 55 |
S | Apple | 20 | 30 |
S | Apple | 10 | 30 |
S | Tesla | 5 | 5 |
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
Solved! Go to Solution.
Hi @NNaj
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
Hi @NNaj
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |