multiple value in one cell (split columns/ text strings) question

[Anonymous]

Hi, 

I have a columns with muliple industries (see columns 1), but this is not possible to split with delimiter (such as comma) because in the sector name there are also comma's. My next attempt was to do it via Text.contains function, but the list of industries contains alot of similar text which results in alot of duplicates (see solumn 2). Do you have any other suggestions to split these industries?

Thank you in advance.

[KT_Bsmart2gethe]

Hi @Anonymous ,

Is the result below what you're looking for?

If yes,

The transformation steps are:

1. Replace ", " with "__" (This is to prevent some of the items get split. We will change it back later)

2. Split Column by Comma

3. Replace "__" back to ", "

4. Split each of the split columns by " - ". This is to get the Industry and Sub-industry.

Let me know if this is what you are looking for. If yes, and you are interested in the dynamic way, then drop me a message and I will share the dynamic way with you.

Regards

KT

[Anonymous]

Hi Rohit, 

In row 8: the correct extract would be: services - Business related services (sub-industry 1) and ICT- it services(software) (sub-industry 2).

The output I want:

Main Industry	sub-industry 1	sub-industry 2
Services - Business Related Services,ICT - IT services (software)	Services - Business Related Services	ICT - IT services (software)

Table in text format: 

Industry

Transportation & Logistics

High Tech Systems - Hardware devices & supporting software

Wholesale, retail and trading

Services

Services - Business Related Services,ICT - IT services (software)

Life Sciences & Health,High Tech Systems

Creative Industries

Creative Industries - Advertising and new media

Services - Business Related Services

Agri-Food - Food and Beverage

Agri-Food

High Tech Systems - Hardware devices & supporting software,ICT - IT services (software)

Construction & Infrastructure

ICT - Future telecom networks and services (Mobile, 4G/5G)

Education

Creative Industries - Entertainment & Media

Creative Industries - Entertainment & Media,Creative Industries - Advertising and new media,ICT

ICT - IT services (software)

Creative Industries - Advertising and new media,Wholesale, retail and trading - E-commerce

Sport, Tourism and Leisure,ICT - IT services (software)

[rohit_singh · Accepted solution]

Hi @Anonymous ,

Thanks for the additional details. Please try this and see if it suits your requirements. You can copy paste the code below into a blank query and see the steps in detail

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVNNTwIxEP0rE06aLJEY9Q6Ej03gIpt4QA61HXYn7rak00L897ZdVzFGkMTTptM3b/rem12ve4UVmnfGOuHIaHj2g8HtAyxMSexIcm+TrXtzKisoUFawemOHDUMf5sKqg7AICvckkbtO9rvIRroENlsXIYnjqTI1sqgxA4tOUA1CK3BWqABNiBXaxPTtECaNPJNGZnjEWjhU0N1l+bgI93kB3KGvupnXiWVBW4SVJNRHL5yjqF2V/RCVOsYWgxF7hFwrz84S/loPo4dqj0ErR7VRjsYDNKhI/FlDAg5LS/2pMSog0ydyjTBwixK/I/4jj/O+jY0OGr08Xolcb61oq/4j05Zn6mMBHNYoTRM8cAdjXzmJ+BqwNC8Uw7+b3dzP2ikT5WXauhMOT7QLDgvSDWrXPWX5afGlTdmFOUarjqSesuxC4pP/Q9TQD2Y2aCVCu0wxxQwK4y1xk8ALJPZn49y8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Industry = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Industry", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Industry], ", ") then Text.Replace([Industry], ", ", "+") else [Industry]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.1", "sub-industry 1"}, {"Custom.2", "sub-industry 2"}, {"Custom.3", "sub-industry 3"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","+",",",Replacer.ReplaceText,{"sub-industry 1", "sub-industry 2", "sub-industry 3"})
in
    #"Replaced Value"

This is the output

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[rohit_singh]

Hi @Anonymous ,

Could you please clarify what part of the string do you want to extract? 
Suppose I take row 8
"services - business related services, ict - it services(software)"

What is your expected output?
Also, please ensure you send across sample data in text format and not screenshots.

Kind regards,

Rohit