Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
64 | |
27 | |
18 | |
13 |