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
NiugeS
Helper V
Helper V

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
Anonymous
Not applicable

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

View solution in original post

7 REPLIES 7
AilleryO
Memorable Member
Memorable Member

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,

 

@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
Not applicable

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

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

@Anonymous Thank you so much ! 


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

@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
Not applicable

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

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.