Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WLou
Helper I
Helper I

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

 

 

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors