Forum Discussion
Solved
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 ...
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
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
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