How do I mailing address into proper columns from address from Sharepoint list

[ResearchDev]

The column has this info

"{""DisplayName"":""7708 Budding Vine Lane, Knoxville, TN 37931"",""EntityType"":""Custom""}"
"{""EntityType"":""PostalAddress"",""LocationSource"":""Bing"",""LocationUri"":""https://www.bingapis.com/api/v6/geoentities?streetaddress=7305+Foxlair+Rd&locality=Knoxville&subregi..."

What is the best approach to extracting this into specific columns (#, Street name, City, State, Zip code)

[PhilipTreacy · Accepted solution]

Hi @ResearchDev 

Download example PBIX file with this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY9La8MwEIT/itA1RnJjGreBUJI+Lg2htEkvkQ+KtbgLsmT0iGNK/3tVGwrtbdiZ/Wb3eKSfgj6g77QcdrIFQZeClmV+QzZRKTQNeUcDZCsNZOTZ2MsZtU5yvyNFeVtcCZoJ+mgChmE/dNP6ffTBtoJ+0Sob+f/8F+uD1GulHHg/Ara2lgGtebPR1VNok7r/eAeHo/ERQueXnPd9z04pJDv0rLYtT4KfF7wBCz99CP7OBwcQ5NS0Kov8evZkL1qim70qEfN8vtAJr9N1q9/nprmPJwcNMsZoVX0D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Address", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not Text.Contains([Address], "LocationUri")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Address", Splitter.SplitTextByDelimiter("""", QuoteStyle.None), {"Address.1", "Address.2", "Address.3", "Address.4", "Address.5", "Address.6", "Address.7", "Address.8", "Address.9"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Address.1", type text}, {"Address.2", type text}, {"Address.3", type text}, {"Address.4", type text}, {"Address.5", type text}, {"Address.6", type text}, {"Address.7", type text}, {"Address.8", type text}, {"Address.9", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Address.4"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Other Columns", "Address.4", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Address.4.1", "Address.4.2", "Address.4.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Address.4.1", type text}, {"Address.4.2", type text}, {"Address.4.3", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Address.4.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Address.4.1.1", "Address.4.1.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Address.4.1.1", Int64.Type}, {"Address.4.1.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type3",{{"Address.4.3", Text.Trim, type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Trimmed Text", "Address.4.3", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Address.4.3.1", "Address.4.3.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Address.4.3.1", type text}, {"Address.4.3.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type4",{{"Address.4.1.1", "Number"}, {"Address.4.1.2", "Street"}, {"Address.4.2", "City"}, {"Address.4.3.1", "State"}, {"Address.4.3.2", "Zip Code"}})
in
    #"Renamed Columns"

Before

After

Regards

Phil