Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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 ...
Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    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! 🙂

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 Industrysub-industry 1sub-industry 2
Services - Business Related Services,ICT - IT services (software)Services - Business Related ServicesICT - 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's avatar
rohit_singh
Solution Sage
4 years ago

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! 🙂