Split Columns with matching value in multiple rows

[NiugeS]

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

[AilleryO]

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,

[NiugeS]

@AilleryO Thank you however I have about 100k of rows with 1000s of Type As and haven't been able to get the result desired.

[Anonymous · Accepted solution]

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

[NiugeS]

@Anonymous YEEKS!  Many thanks - i'll have a go!

[NiugeS]

@Anonymous Thank you so much ! 

That works on the data i provided and will now try with the data I have.  Huge thank you!

[NiugeS]

@Anonymous  Many thanks I think I have it working.  Can I confirm that null values in Result Achieved.2 would confirm that Type B isn't able to find a Type A?  I assume there must be some typos in the data.

[Anonymous]

This can be null only if Name has the B type and has no "-" in the name. You should check the quality of your data.

Best
D