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 col...
Need Help
Show and Tell
Tips and Tricks
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.