Forum Discussion
Split Columns with matching value in multiple rows
Hi - I'm new to Power BI and not sure if this is possible. I've searched through the forum but might be searching for the wrong terms.
I have the following Example data in columns where in the Name column there is the family name and also subsets. The Type distinguishes the Family name from the subsets (Type A family name Type B subset). I am trying to find a way to get 'Result Wanted'. I've tried transforming data, using text delimiters etc but as there are so many variables, haven't had any success. Any help appreciated.
| Name | Type | Result Wanted |
| ABC | A | ABC |
| ABC-Test | B | ABC |
| ABC - Run | B | ABC |
| ABC - Tested | B | ABC |
| DEF-G | A | DEF-G |
| DEF-G - Testers | B | DEF-G |
| DEF-G-break | B | DEF-G |
| DEF-G - Tester | B | DEF-G |
| H I J - K L | A | H I J - K L |
| H I J - K L - Tester | B | H I J - K L |
| H I J - K L - break | B | H I J - K L |
| H I J - K L - Run | B | H I J - K L |
Here's the M code to do it. Paste into the Advanced Editor in Power Query and examine the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcgRhICtWByyiG5JaXAIUckIVVtBVCCrNwyoO0pCagibl4uqm6w41HcKGi8K0FBVD9aDJ6yYVpSZmY5eD68WQ9lDwVPACSnsr+ECtRRZBU4FuDH6lyO7BrxIRRCjqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, #"Result Wanted" = _t]), #"Added Custom" = Table.AddColumn(Source, "Result Achieved", each if [Type] = "A" then [Name] & " - ARTIFICIAL" else [Name]), #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Result Achieved", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Result Achieved.1", "Result Achieved.2"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Result Achieved.2"}), #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Result Achieved.1", Text.Trim, type text}}), #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Result Achieved.1", "Result Achieved"}}) in #"Renamed Columns"Best
D
7 Replies
Hi,
You should get the result you want with Split Column.
Choose delimiter - and tick, Most Right Delimiter.
It should work.
Otherwise try with Columns from Example, and type in the first values you want.
Have a nice day,
Here's the M code to do it. Paste into the Advanced Editor in Power Query and examine the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcgRhICtWByyiG5JaXAIUckIVVtBVCCrNwyoO0pCagibl4uqm6w41HcKGi8K0FBVD9aDJ6yYVpSZmY5eD68WQ9lDwVPACSnsr+ECtRRZBU4FuDH6lyO7BrxIRRCjqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, #"Result Wanted" = _t]), #"Added Custom" = Table.AddColumn(Source, "Result Achieved", each if [Type] = "A" then [Name] & " - ARTIFICIAL" else [Name]), #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Result Achieved", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Result Achieved.1", "Result Achieved.2"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Result Achieved.2"}), #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Result Achieved.1", Text.Trim, type text}}), #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Result Achieved.1", "Result Achieved"}}) in #"Renamed Columns"Best
D
