Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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