Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Split column based on conditions

Hi all

I am trying to split a city column which is not consistant across all, some ends with a number and some doesn't. I am basically trying to split the city text and number into two different columns. I have tried delimiter, non-digit to digit options etc but it doesn't work. I have more than 1 million records so looking for an effective and easy way. Wherever there is no city ID/number i will leave it blank otherwise keep the city ID. Any ideas how to do that?

 

City
LA-VILLE PLATTE - 1003002
CA-CITYOFIND-4040SCAP(US18) - 1003010
MI-MUSKEGON-2076 NORTHWOODS DR
North Dallas (TX) - 1002010
Denver - Colorado
CITYOFIND - A117 - CA

 

Thanks in advance.

Need Help
Show and Tell
Tips and Tricks

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    If your numbers to be removed all start with 100, you could use this approach.  It splits the column on " - 100", and then adds "100 as a prefix to the #column.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY7RCoIwGIVf5WdXBg3+mWS3Y7MazU3ctEK8EBK6kAYWPX9meXvOx3dO0xDNaa20zqDQ3PsMKDDEDWJM2nVDBKdC+avdKyNpggk6wYuocmy3WkiGM5krmlfulB2soTGmWzC29MeztdKBLGfEhPF1B9kNQ/eEyF/+inhRyP7x7scpFGEIY3cLvwvL/lRwxtJvz0nbfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "City", Splitter.SplitTextByEachDelimiter({" - 100"}, QuoteStyle.Csv, false), {"City.1", "City.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"City.1", type text}, {"City.2", Int64.Type}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type1", {{"City.2", each "100" & Text.From(_, "en-US"), type text}})
in
    #"Added Prefix"

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      MO , unfortunately it is not starting with the same number sequence. For now we did the split in Excel file manually.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        Hi,

        This M code works

        let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([City], "- "), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Text After Delimiter", "Custom", each Value.Is(Value.FromText([Text After Delimiter]), type number)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.PositionOf([City], [Text After Delimiter])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom]=true then Text.Start([City],[Custom.1]-3) else [City]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Text After Delimiter", "Custom", "Custom.1"})
in
    #"Removed Columns"

        Hope this helps.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous - So given the below sample data, what is your desired output?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler , below is the desired output. I want get rid of the City ID wherever exists otherwise just display the text as City.

       

      Desired Output
      LA-VILLE PLATTE
      CA-CITYOFIND-4040SCAP(US18)
      MI-MUSKEGON-2076 NORTHWOODS DR
      North Dallas (TX)
      Denver - Colorado
      CITYOFIND - A117 - CA