Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SaberSuM
Frequent Visitor

Power Query Help

Hi,

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

SaberSuM_0-1717476935615.png

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 CentreStaffBCDE
446Salary1000AEmployee A    
447Annual Leave100AEmployee A    
449Superannuation100AEmployee A    
446Salary2000BEmployee B 10001000 
447Annual Leave200BEmployee B 100100 
449Superannuation100BEmployee B 5050 
446Salary2500CEmployee C  2000500
447Annual Leave100CEmployee C  8020
449Superannuation100CEmployee C  8020

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

 

Desired Output:

SaberSuM_2-1717477222406.png

Thank you in advance

 

*Edit: added sample data

1 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

vxinruzhumsft_0-1717642619693.png

 

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.

View solution in original post

5 REPLIES 5
SaberSuM
Frequent Visitor

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

 

Sample Data

 

Account CodeAccount NameAmountCost CentreStaffBCDE
446Salary1000AEmployee A    
447Annual Leave100AEmployee A    
449Superannuation100AEmployee A    
446Salary2000BEmployee B 10001000 
447Annual Leave200BEmployee B 100100 
449Superannuation100BEmployee B 5050 
446Salary2500CEmployee C  2000500
447Annual Leave100CEmployee C  8020
449Superannuation100CEmployee C  8020
ManuelBolz
Responsive Resident
Responsive Resident

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
Super User
Super User

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

 

Sample Data

 

Account CodeAccount NameAmountCost CentreStaffBCDE
446Salary1000AEmployee A    
447Annual Leave100AEmployee A    
449Superannuation100AEmployee A    
446Salary2000BEmployee B 10001000 
447Annual Leave200BEmployee B 100100 
449Superannuation100BEmployee B 5050 
446Salary2500CEmployee C  2000500
447Annual Leave100CEmployee C  8020
449Superannuation100CEmployee C  8020

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

vxinruzhumsft_0-1717642619693.png

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors