Modify multiple row into column with different value

[siskays]

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!

[wdx223_Daniel]

=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])

[Greg_Deckler · Accepted 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"