Forum Discussion
Split Value by deliminator and split other columns value
So I have a list of Sales reps and their commission amounts. There are a few cases where multiple sales reps worked on the same job and split the commission 50/50. How do I go about splitting the sales reps out into single Reps and then also splitting the Commission amount by 50% for each Rep so I still have accurate data?
Something similar to this:
Here is one way to do it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1UNJRctRxUorViVYyNwVznMEcCwjHCcSNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Commision Amount" = _t, #"Sales Rep" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Commision Amount", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Commision Amount] / (Text.Length(Text.Select ([Sales Rep], ",")) + 1), type number), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Commision Amount"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Sales Rep"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Commision Amount"}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Sales Rep", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sales Rep") in #"Split Column by Delimiter"Before
After
6 Replies
Here is one way to do it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1UNJRctRxUorViVYyNwVznMEcCwjHCcSNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Commision Amount" = _t, #"Sales Rep" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Commision Amount", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Commision Amount] / (Text.Length(Text.Select ([Sales Rep], ",")) + 1), type number), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Commision Amount"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Sales Rep"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Commision Amount"}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Sales Rep", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sales Rep") in #"Split Column by Delimiter"Before
After
Create new blank query.
Replace all text in new query with code above and you can follow the steps.
The key is in the 3rd step. It is counting number of comas. Adding 1 on count we get the count of Sales Rep...