Power Query Help

[SaberSuM]

Hi,

I have combined multiple excel files using power query that resulted in this.

I have also merged another table on top of the current one, which requires me to split some employees' pay for that period. For example, employee B needs to split his cost to Cost Centre C & D by 50%, and employee C needs to split his cost across cost centre D & E.

Sample Data

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

Is there anyway I can transform the table and give me the result by cost centre, account code, and name without duplicating.

Desired Output:

Thank you in advance

*Edit: added sample data

[SaberSuM]

Correct, 3rd table is the output i am looking for.

Sample Data

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

[ManuelBolz]

Hallo @SaberSuM,

i'm not sure if I understood your question correctly. But maybe the following code will help you.

let
    Source = [YOUR DATASOURCE],
    Datatype = Table.TransformColumnTypes(Source ,{{"Cost Centre", type text}, {"Account Code", Int64.Type}, {"Account Name", type text}, {"Debit", Int64.Type}}),
    Group = Table.Group(Datatype, {"Cost Centre"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Account Code", each Table.RowCount(_), Int64.Type}})
in
    Group

 
Did I answer your question? Please, mark my post as a solution!

[dufoq3]

Hi, last (3rd) table shoud be the output? If yes, provide sample data in usable format (not as a screenshot please).

[SaberSuM]

Correct, 3rd table is the output i am looking for.

Sample Data

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

[Anonymous · Accepted solution]

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.