Power Query Help

Hi,

Thanks for the solutions ManuelBolz  and dufoq3  provided, and i want to offer some more informationf for user to refer to.

hello SaberSuM , you can create a blank query and put the following code to advanced editor .

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjExU9JRCk7MSSyqBDIMDQwMgJQjELvmFuTkV6amKoA4Cig4Vgek0RykMC+vNDFHwSc1sSwVop1Y3ZYga0sLUosSQUaUZObnkaYfxdlGEGc7IWt0gmqAeglK4XG8EV4z4CQRHsBqhqkBjMDuBVOwVmdkrc5I/oZ6EaSKQPDjNMLCAGwOYfcTNiEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Code" = _t, #"Account Name" = _t, Amount = _t, #"Cost Centre" = _t, Staff = _t, B = _t, C = _t, D = _t, E = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Code", Int64.Type}, {"Account Name", type text}, {"Amount", Int64.Type}, {"Cost Centre", type text}, {"Staff", type text}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"B", "C", "D", "E"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "A", each if [Cost Centre] = "A" then [Amount] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Cost Centre", "Amount"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Staff", "Account Name", "Account Code"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Account Code", "Account Name", "Attribute"}, {{"Sum", each List.Sum([Value]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.