Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi all, I have this data table on Power Query:
Account A | 1000 |
Charges | 1500 |
Discount 1 | -400 |
Discount 2 | -200 |
Tax | 100 |
Account B | 2000 |
Charges | 3000 |
Discount 1 | -1500 |
Tax | 500 |
And I want to change above table into this:
Account Name | Final Charges | Charges | Discount 1 | Discount 2 | Tax |
Account A | 1000 | 1500 | -400 | -200 | 100 |
Account B | 2000 | 3000 | -1500 | 0 | 500 |
Final Charges is the total of Charges, Discount 1, Discount 2, and Tax.
How do I change the previous table format to the table format I want in Power Query? Thank you!
Solved! Go to Solution.
@siskays Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1FwVNJRMjQwMFCK1YlWcs5ILEpPLQYJmUKFXDKLIQoNgaK6JuiiRiBRI6hoSGIFxDQwD2aDE1DMCNMGYwOsNsAthhgG5sUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}, {"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column], "Account") then [Column] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Custom]), "Custom", "Column1", List.Sum),
#"Transposed Table" = Table.Transpose(#"Pivoted Column"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account A", Int64.Type}, {"Account B", Int64.Type}, {"Charges", Int64.Type}, {"Discount 1", Int64.Type}, {"Discount 2", Int64.Type}, {"Tax", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Charges", "Discount 1", "Discount 2", "Tax"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Account Name"}, {"Value", "Final"}})
in
#"Renamed Columns"
=Table.Combine(Table.Group(PreviousStepName,"Column1",{"n",each let a=Table.ToColumns(_) in #table({"Account Name","Final Charges"}&List.Skip(a{0}),{{a{0}{0}}&a{1}})},0,(x,y)=>Byte.From(Text.StartsWith(y,"Account")))[n])
@siskays Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1FwVNJRMjQwMFCK1YlWcs5ILEpPLQYJmUKFXDKLIQoNgaK6JuiiRiBRI6hoSGIFxDQwD2aDE1DMCNMGYwOsNsAthhgG5sUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}, {"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column], "Account") then [Column] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Custom]), "Custom", "Column1", List.Sum),
#"Transposed Table" = Table.Transpose(#"Pivoted Column"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account A", Int64.Type}, {"Account B", Int64.Type}, {"Charges", Int64.Type}, {"Discount 1", Int64.Type}, {"Discount 2", Int64.Type}, {"Tax", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Charges", "Discount 1", "Discount 2", "Tax"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Account Name"}, {"Value", "Final"}})
in
#"Renamed Columns"
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |