Spreading rows based on division percentages from another table

[JayWai]

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: 

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

Thanks in advance! 

[JayWai]

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

[BA_Pete]

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

[JayWai]

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 Name	Cost Type	Amount
Frank	Fuel	50
Frank	Parking	20
Jennifer	Fuel	200
Jennifer	Catering	100
Toby	Fuel	100

Employee Name	%	BU	Dept
Frank	50%	Furniture	Sales
Frank	50%	Design	Sales
Jennifer	30%	Overhead	Management
Jennifer	20%	Furniture	Support
Jennifer	50%	Furniture	Sales
Toby	100%	Overhead	Management

Employee Name	Cost Type	Amount	BU	Dept
Frank	Fuel	25	Furniture	Sales
Frank	Fuel	25	Design	Sales
Frank	Parking	10	Furniture	Sales
Frank	Parking	10	Design	Sales
Jennifer	Fuel	60	Overhead	Management
Jennifer	Fuel	40	Furniture	Support
Jennifer	Fuel	100	Furniture	Sales
Jennifer	Catering	30	Overhead	Management
Jennifer	Catering	20	Furniture	Support
Jennifer	Catering	50	Furniture	Sales
Toby	Fuel	100	Overhead	Management

In any case thanks for your time and reply! 

Kind regards, 

[BA_Pete · 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:

I've attached the working PBIX below.

Pete