none
Split/ Transform Column Dynamically RRS feed

Answers

  • As a function SplitAddress:

    (address as text, extractText as text) =>
    let
        TextPosition = Text.PositionOf(address, extractText, Occurrence.Last, Comparer.OrdinalIgnoreCase),
        StreetAddress = try Text.Start(address, TextPosition - 1) otherwise address,
        City = try Text.Middle(address, TextPosition) otherwise null,
        FullAddress = [Street = StreetAddress, City = City]
    in
        FullAddress

    Added as a custom column to a table with an Address column:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        AddedCustom = Table.AddColumn(Source, "Custom", each SplitAddress([Address], "CH")),
        ExpandedCustom = Table.ExpandRecordColumn(AddedCustom, "Custom", {"Street", "City"})
    in
        ExpandedCustom

    • Marked as answer by aklaur Friday, March 17, 2017 9:36 PM
    Thursday, March 16, 2017 6:55 PM