Split column based on conditions

[Anonymous]

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.

[mwegener]

Hi @Anonymous ,

I love "Column From Examples" for this kind of task.

GuyInACube - 6 ways to go bananas with Column from Examples in Power BI

https://youtu.be/GUwtPIKtqO0

[mahoneypat]

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]

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

[Ashish_Mathur]

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]

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

[Anonymous]

@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