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