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 CodeAccount NameAmountCost CentreStaffBCDE446Salary1000AEmployee A 447Annual Leave100AEmployee A 449Superannuation100AEmployee A 446Salary2000BEmployee B 10001000 447Annual Leave200BEmployee B 100100 449Superannuation100BEmployee B 5050 446Salary2500CEmployee C 2000500447Annual Leave100CEmployee C 8020449Superannuation100CEmployee C 8020Is 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

Frequent Visitor

2 years ago

Solved

Power Query Help

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

Need Help

Anonymous

2 years ago

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.

Power Query Help.pbix26 KB

5 Replies

dufoq3

Community Champion

2 years ago

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

SaberSuM

Frequent Visitor

2 years ago

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

Not applicable

2 years ago

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.

Power Query Help.pbix26 KB

ManuelBolz

Responsive Resident

2 years ago

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!

SaberSuM

Frequent Visitor

2 years ago

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

Forum Discussion

Power Query Help

5 Replies

Related content

Power Query dates

Data transform using power query

ayuda en power query

Power BI downloaded PBIX does not contain Power Query queries

Power Query