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

M Function to split dynamic delimeter

Hi there

 

I got a situation where i need to be able to split a colum (long string -> product description) into 2 columns. However the delimeter will be determined by another column. For example

 

Product Description                                                  Product Code

Iphone S4, A1-0315-0123                                         A1

Training 01/04/2016, S1-xxx-xxx 1 attendee            S1

 

THe reason I cant go with comma delimeter or space or any other type is because of bad data input, the Product Code can happen at the beginning, the end of the middle of Product Description. However the combination is always the same that goes by ProductCode-mmyy-XXXX.

 

Hope you can advice.

 

Pedzilla

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

The code below inserts a delimiter before the productcode, i.e. it searches for the productocde followed by "-" (e.g. "A1-").

This part is coded via the advanced editor.

After that, the column is splitted using standard menu options.

 

let
    Source = Table1,
    #"Replaced Value" = Table.ReplaceValue(Source,each [Product Code]&"-",each "ThisIsNowTheDelimiter"&[Product Code]&"-",Replacer.ReplaceText,{"Product Description"}),
    RestoredColumnTypes = Value.ReplaceType(#"Replaced Value",Value.Type(Source)),
    #"Split Column by Delimiter" = Table.SplitColumn(RestoredColumnTypes, "Product Description", Splitter.SplitTextByEachDelimiter({"ThisIsNowTheDelimiter"}, QuoteStyle.Csv, false), {"Product Description.1", "Product Description.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product Description.1", type text}, {"Product Description.2", type text}})
in
    #"Changed Type"
Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
MarcelBeug
Community Champion
Community Champion

The code below inserts a delimiter before the productcode, i.e. it searches for the productocde followed by "-" (e.g. "A1-").

This part is coded via the advanced editor.

After that, the column is splitted using standard menu options.

 

let
    Source = Table1,
    #"Replaced Value" = Table.ReplaceValue(Source,each [Product Code]&"-",each "ThisIsNowTheDelimiter"&[Product Code]&"-",Replacer.ReplaceText,{"Product Description"}),
    RestoredColumnTypes = Value.ReplaceType(#"Replaced Value",Value.Type(Source)),
    #"Split Column by Delimiter" = Table.SplitColumn(RestoredColumnTypes, "Product Description", Splitter.SplitTextByEachDelimiter({"ThisIsNowTheDelimiter"}, QuoteStyle.Csv, false), {"Product Description.1", "Product Description.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product Description.1", type text}, {"Product Description.2", type text}})
in
    #"Changed Type"
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Hi there
Thank you for your repl @MarcelBeug
Problem is my product description might have more than one "-" occasionally different operator put that in for example "training - powerbi day 1, tr-0212-0123"
Do you think this code can cope with this irregularity?

Yes, it can.

There will only be a problem if the combination of the code with the "-" would appear multiple times, e.g.

"tr- powerbi day 1, tr-0212-0123"

Specializing in Power Query Formula Language (M)

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.