cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JayWai
New Member

Spreading rows based on division percentages from another table

Hi, 

Sorry if this is handled elsewhere, I looked around but couldn't really find a good topic on what I'm looking for. 

 

 

I'm looking for a way to duplicate and recalculate some data based on another table. 

 

The case is the following: 

 

In one Query I generate a list with employee costs in several categories, with an amount.

 

In another table I have employee data, assigning each employee to 1 or more Business Units and departments with a split of 100% over 1 or more lines.

 

The expected output is that each cost is duplicated X times where X is the amount of different lines the employee is assigned to, mentioning the BU and Dept of each line and multiplying the amounts with the % factor.

 

 

Small example to show what I'm looking for: 

 

 

 

JayWai_0-1669720962532.png

 

Looking for some guidance on how to generate the expected output table. 

 

 

Thanks in advance! 

 

 

1 ACCEPTED SOLUTION

 

Given this table called 'Input2':

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzMtW0lEyNVAFkm6lRXmZJaVFqUB2cGJOarFSrA66GpfU4sz0PBQFXql5eZlpqUVAQWOwGv+y1KKM1MQUINM3MS8xPTU3Na8EXakRppWlBQX5RRgK8bktJD+pEihgaIDP3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"% " = _t, BU = _t, Dept = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"% ", Percentage.Type}, {"BU", type text}, {"Dept", type text}})
in
    chgTypes

 

You can create this table based on Input1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzMtW0lFyK03NAVKmBkqxOgjRgMSi7My8dCDLCCLhlZqXl5mWWoTQYWSAIeOcWJJaBNFmCJUNyU+qROgBi8YCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Cost Type" = _t, Amount = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Cost Type", type text}, {"Amount", Int64.Type}}),
    mergeInput2 = Table.NestedJoin(chgTypes, {"Employee Name"}, Input2, {"Employee Name"}, "Input2", JoinKind.LeftOuter),
    expandInput2 = Table.ExpandTableColumn(mergeInput2, "Input2", {"% ", "BU", "Dept"}, {"% ", "BU", "Dept"}),
    addSplitAmount = Table.AddColumn(expandInput2, "splitAmount", each [Amount] * [#"% "]),
    remOthCols = Table.SelectColumns(addSplitAmount,{"Employee Name", "Cost Type", "BU", "Dept", "splitAmount"})
in
    remOthCols

 

Output:

BA_Pete_0-1669734292769.png

 

I've attached the working PBIX below.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
JayWai
New Member

Thanks Pete, Crystal clear and will help me to work on the actual Query. Much obliged! 

 

BA_Pete
Super User
Super User

Hi @JayWai ,

 

You should just be able to merge Input2 to Input1 on [Employee Name], then multiply Input1[Amount] by Input2[%] to get the new amount column.

Remove any columns you no longer need.

 

If you can share some sample data of Input1 an Input2 in a copyable format, I can knock up a working query for you.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hey Pete, 

 

Thanks for your input. I copied the "data" attached. It's just a simplified and short version of what I'm actually working with. Just want to get the technique down... Probably relatively simple. 

 

When merging, won't I just add one of the hits of Frank for example to his costs, instead of showing each line for Frank available? 

 

Employee NameCost TypeAmount
FrankFuel50
FrankParking20
JenniferFuel200
JenniferCatering100
TobyFuel100

 

Employee NameBUDept
Frank50%FurnitureSales
Frank50%DesignSales
Jennifer30%OverheadManagement
Jennifer20%FurnitureSupport
Jennifer50%FurnitureSales
Toby100%OverheadManagement

 

Employee NameCost TypeAmountBUDept
FrankFuel25FurnitureSales
FrankFuel25DesignSales
FrankParking10FurnitureSales
FrankParking10DesignSales
JenniferFuel60OverheadManagement
JenniferFuel40FurnitureSupport
JenniferFuel100FurnitureSales
JenniferCatering30OverheadManagement
JenniferCatering20FurnitureSupport
JenniferCatering50FurnitureSales
TobyFuel100OverheadManagement

 

In any case thanks for your time and reply! 

 

Kind regards, 

 

 

Given this table called 'Input2':

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzMtW0lEyNVAFkm6lRXmZJaVFqUB2cGJOarFSrA66GpfU4sz0PBQFXql5eZlpqUVAQWOwGv+y1KKM1MQUINM3MS8xPTU3Na8EXakRppWlBQX5RRgK8bktJD+pEihgaIDP3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"% " = _t, BU = _t, Dept = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"% ", Percentage.Type}, {"BU", type text}, {"Dept", type text}})
in
    chgTypes

 

You can create this table based on Input1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzMtW0lFyK03NAVKmBkqxOgjRgMSi7My8dCDLCCLhlZqXl5mWWoTQYWSAIeOcWJJaBNFmCJUNyU+qROgBi8YCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Cost Type" = _t, Amount = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Cost Type", type text}, {"Amount", Int64.Type}}),
    mergeInput2 = Table.NestedJoin(chgTypes, {"Employee Name"}, Input2, {"Employee Name"}, "Input2", JoinKind.LeftOuter),
    expandInput2 = Table.ExpandTableColumn(mergeInput2, "Input2", {"% ", "BU", "Dept"}, {"% ", "BU", "Dept"}),
    addSplitAmount = Table.AddColumn(expandInput2, "splitAmount", each [Amount] * [#"% "]),
    remOthCols = Table.SelectColumns(addSplitAmount,{"Employee Name", "Cost Type", "BU", "Dept", "splitAmount"})
in
    remOthCols

 

Output:

BA_Pete_0-1669734292769.png

 

I've attached the working PBIX below.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors