Need help with Power Query

[brian_c999]

Hi,

Apologies for multiple posts as my previous one was tagged as spammed and I could not reply.

Is there anyway to transform the sample data into the second table below. Basically, some employees need to split based on a ratio to other cost centres (B, C, D or E). Using Staff B as an example, his costs should be split across cost centres C & D while no costs should remain at cost centre B.

Account Code	Account Name	Amount	Cost Centre	Names	B	C	D	E
446	Salary	1000	A	Staff A
447	Annual Leave	100	A	Staff A
449	Superannuation	100	A	Staff A
446	Salary	2000	B	Staff B		1000	1000
447	Annual Leave	200	B	Staff B		100	100
449	Superannuation	100	B	Staff B		50	50
446	Salary	2500	C	Staff C			2000	500
447	Annual Leave	100	C	Staff C			80	20
449	Superannuation	100	C	Staff C			80	20

Desired Output:

Cost Centre	Account Code	Account Name	Net
A	446	Salary	1000
A	447	Annual Leave	100
A	449	Superannuation	100
B	446	Salary	0
B	447	Annual Leave	0
B	449	Superannuation	0
C	446	Salary	1000
C	447	Annual Leave	100
C	449	Superannuation	50
D	446	Salary	3000
D	447	Annual Leave	180
D	449	Superannuation	130
E	446	Salary	500
E	447	Annual Leave	20
E	449	Superannuation	20

Thank you in advance

[lbendlin · Accepted solution]

You need to modify your source table to include the destination for all rows.

And then you can remove all the redundant columns

Having the split in columns is generally bad practice for Power BI (common for Excel data) - maintenance red flag.

Anyway, here is the transform :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjExU9JRCk7MSSyqBDIMDQwMgJQCCo7VAakzB7Id8/JKE3MUfFITy1IhqnEotgQZWlqQWpQI0lGSmZ+HVzmKG6ByUKfAXYTTFQj1SHbgcQZUvakBjMDnChA2gjjBFEgScgQIWxiA9RDhBBTVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Code" = _t, #"Account Name" = _t, A = _t, B = _t, C = _t, D = _t, E = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account Code", "Account Name"}, "Cost Centre", "Net"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Net", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cost Centre", "Account Code", "Account Name"}, {{"Net", each List.Sum([Net]), type text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Cost Centre", Order.Ascending}, {"Account Code", Order.Ascending}})
in
    #"Sorted Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.