Split value into multiple rows based on percentage

[WLou]

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

[mahoneypat · Accepted 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

[NSRjecross]

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