cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NiugeS Helper IV
Helper IV

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.

NameTypeResult Wanted
ABCAABC
ABC-TestBABC
ABC - RunBABC
ABC - TestedBABC
DEF-GADEF-G
DEF-G - TestersBDEF-G
DEF-G-breakBDEF-G
DEF-G - TesterBDEF-G
H I J - K LAH I J - K L
H I J - K L - TesterBH I J - K L
H I J - K L - breakBH I J - K L
H I J - K L - RunBH I J - K L
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Split Columns with matching value in multiple rows

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



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

7 REPLIES 7
AilleryO Helper V
Helper V

Re: Split Columns with matching value in multiple rows

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 Helper IV
Helper IV

Re: Split Columns with matching value in multiple rows

@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.

Super User IV
Super User IV

Re: Split Columns with matching value in multiple rows

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



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

NiugeS Helper IV
Helper IV

Re: Split Columns with matching value in multiple rows

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

NiugeS Helper IV
Helper IV

Re: Split Columns with matching value in multiple rows

@darlove Thank you so much ! 


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

NiugeS Helper IV
Helper IV

Re: Split Columns with matching value in multiple rows

@darlove  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.

Super User IV
Super User IV

Re: Split Columns with matching value in multiple rows

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


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors