Forum Discussion
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:
Looking for some guidance on how to generate the expected output table.
Thanks in advance!
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 chgTypesYou 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 remOthColsOutput:
I've attached the working PBIX below.
Pete
4 Replies
Thanks Pete, Crystal clear and will help me to work on the actual Query. Much obliged!
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
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,
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 chgTypesYou 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 remOthColsOutput:
I've attached the working PBIX below.
Pete
