Create single column for zip codes

[basel144]

hi,

i have data like this and want to create one column for all zip codes.  I think I can split the codes into seperate columns but after that I'm stuck.

thank you

[PhilipTreacy · Accepted solution]

Hi @basel144 

Download example PBIX with data shown below

First, right click on the column header and select Split Column -> By delimiter

Choose comma as the delimiter and Split At each occurrence

Which gives you this

Right click on the column header of the first column, and click on Unpivot Other Columns

Then tidy up by deleting the Attribute column and renaming the other columns

Regards

Phil

[danextian · Accepted solution]

Hi @basel144 

Splitting the Address column by comma into several columns is a safe method if you don't expect more zip codes in the future than what you currently have  for each location. For future-proofing, I would use Text.Split after extracting the zip codes from the address.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcy9DsIgGIXhWzHM3wC0/I0a4+Rk3AgDIqlNDRhAr1/4ujzDe5JjLTmm8MrFLxGMEXRGOXFgycWv5eHTVntT1KBsyPb9/g01J9BCMYrhurb2jodbDhsozvvZUKBqyNh+XGJNGczUE4ZzTL9YQFPOzHAymE9lfS7xk0sDKiWV6IwKVBPn/g==", 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}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Zip", each Text.AfterDelimiter([Address], ","), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Text After Delimiter", "Split Zip", each Text.Split([Zip], ",")),
    #"Split Zip" = Table.ExpandListColumn(#"Added Custom", "Split Zip"),
    #"Removed Columns" = Table.RemoveColumns(#"Split Zip",{"Zip"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Address", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Split Zip", type text}})
in
    #"Changed Type1"

[PhilipTreacy · Accepted solution]

Hi @basel144 

Download example PBIX with data shown below

First, right click on the column header and select Split Column -> By delimiter

Choose comma as the delimiter and Split At each occurrence

Which gives you this

Right click on the column header of the first column, and click on Unpivot Other Columns

Then tidy up by deleting the Attribute column and renaming the other columns

Regards

Phil