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

 

 

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

4 Replies

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

    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

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    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

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