Forum Discussion
Split value into multiple rows based on percentage
Hi all,
I'm having a little chanllenge here with split one row into multiple based on %
As I'm still constructing the data, I'm trying to get a sense of what function/logic do we need here?
Thanks in advance
Wendy
An example below
Source Table 1 (value)
Cost Code $
| 315-4213 | 5,000 |
| 340-4214 | 10,000 |
| 350-5134 | 2,000 |
Source Table 2 (% split)
Cost Code Split 1 Split 2 Split 3Split 4
| 315-4213 | 30% | 50% | 20% | |
| 340-4214 | 10% | 80% | 5% | 5% |
| 350-5134 | 20% | 70% | 10% |
Result Table
Cost Code $ Split
| 315-4213 | $1,500 |
| 315-4213 | $2,500 |
| 315-4213 | $1,000 |
| 340-4214 | $1,000 |
| 340-4214 | $8,000 |
| 340-4214 | $500 |
| 340-4214 | $500 |
| 350-5134 | $400 |
| 350-5134 | $1,400 |
| 350-5134 | $200 |
Please see this M code for one way to get your desired result. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below. It references a second query called Table1 with your table1 data provided.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY01TUxMjRW0lFSMDZQBVGmEMoIQinF6gBVmRiAVJmABAwh4hZQxTASrMzUQNfU0NgESbs5hDKEGRYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cost Code" = _t, #"Split 1" = _t, #"Split 2" = _t, #"Split 3" = _t, #"Split 4" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost Code", type text}, {"Split 1", Percentage.Type}, {"Split 2", Percentage.Type}, {"Split 3", Percentage.Type}, {"Split 4", Percentage.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Split 1", type text}, {"Split 2", type text}, {"Split 3", type text}, {"Split 4", type text}}, "en-US"),{"Split 1", "Split 2", "Split 3", "Split 4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Splits"), #"Added Custom" = Table.AddColumn(#"Merged Columns", "SplitList", each List.RemoveMatchingItems(Text.Split([Splits], ";"),{""})), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Splits"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Cost Code"}, Table1, {"Cost Code "}, "Table1", JoinKind.LeftOuter), #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"$"}, {"$"}), #"Expanded SplitList" = Table.ExpandListColumn(#"Expanded Table1", "SplitList"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded SplitList",{{"SplitList", type number}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "$ Split", each [SplitList]*[#"$"]), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Cost Code", "$ Split"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"$ Split", Currency.Type}}) in #"Changed Type2"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
2 Replies
Please see this M code for one way to get your desired result. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below. It references a second query called Table1 with your table1 data provided.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY01TUxMjRW0lFSMDZQBVGmEMoIQinF6gBVmRiAVJmABAwh4hZQxTASrMzUQNfU0NgESbs5hDKEGRYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cost Code" = _t, #"Split 1" = _t, #"Split 2" = _t, #"Split 3" = _t, #"Split 4" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost Code", type text}, {"Split 1", Percentage.Type}, {"Split 2", Percentage.Type}, {"Split 3", Percentage.Type}, {"Split 4", Percentage.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Split 1", type text}, {"Split 2", type text}, {"Split 3", type text}, {"Split 4", type text}}, "en-US"),{"Split 1", "Split 2", "Split 3", "Split 4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Splits"), #"Added Custom" = Table.AddColumn(#"Merged Columns", "SplitList", each List.RemoveMatchingItems(Text.Split([Splits], ";"),{""})), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Splits"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Cost Code"}, Table1, {"Cost Code "}, "Table1", JoinKind.LeftOuter), #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"$"}, {"$"}), #"Expanded SplitList" = Table.ExpandListColumn(#"Expanded Table1", "SplitList"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded SplitList",{{"SplitList", type number}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "$ Split", each [SplitList]*[#"$"]), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Cost Code", "$ Split"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"$ Split", Currency.Type}}) in #"Changed Type2"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
As a process it appears the only real issue is the source table 2 needs to be cleaned up then unpivoted so that it reads
costcode|split#|split_pct (as a number)
Then you can join both sources on cost code with the multiply for a result after split value. You can retain the split# from the unpivot in the results (you didn't list it, but would be unusual to not want to know which percentage (column) you were representing)
Jen
