Forum Discussion
Create Column by region with zips
Hi mnarveson ,
We can insert a custom column in power query as below.
Custom = if [Zip Code]>=90000 and [Zip Code]< 93599 then "Southern California" else if [Zip Code]>=94600 and [Zip Code] <=96199 then "Northern California" else "undefined"
Also please find the M code as below.
let Source = Csv.Document(File.Contents("D:\xxxx\xxxx\sel\data.csv"),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"County", type text}, {"Place Name", type text}, {"State", type text}, {"State Abbreviation", type text}, {"Average of Zip Code", type number}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Average of Zip Code", "Zip Code"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Zip Code]>=90000 and [Zip Code]< 93599 then "Southern California" else if [Zip Code]>=94600 and [Zip Code] <=96199 then "Northern California" else "undefined") in #"Added Custom"Please find the pbix as attached.
Regards,
Frank
I created a new pbix with just the zipcode data on it. https://1drv.ms/u/s!AkAaFoxX6vm2t-pLIPgemSGR7jFutg
Solution SpecialistDepending on how many regions you have, that IF could get real messy.
You could try a seperate table with a column for zip and a column with matching region. Then, you could either join the two or us LOOKUPVALUE.
v-frfei-msftCommunity Support
Hi mnarveson ,
To create a calculated column in your pbix.
Column = IF ( GeoZipTable[Zip Code] >= 90000 && GeoZipTable[Zip Code] <= 93599, "Southern California", IF ( GeoZipTable[Zip Code] >= 94600 && GeoZipTable[Zip Code] <= 96199, "Northern California", "undefined" ) )
Also please find the pbix as attached.
Reagrds,
Frank
Thank you very much for the help. I think the issue was I was trying to add the new column in the Power Query editor and not on the Desktop. Is there a way to add this column in Power Query?
- v-frfei-msft
Hi mnarveson ,
We can insert a custom column in power query as below.
Custom = if [Zip Code]>=90000 and [Zip Code]< 93599 then "Southern California" else if [Zip Code]>=94600 and [Zip Code] <=96199 then "Northern California" else "undefined"
Also please find the M code as below.
let Source = Csv.Document(File.Contents("D:\xxxx\xxxx\sel\data.csv"),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"County", type text}, {"Place Name", type text}, {"State", type text}, {"State Abbreviation", type text}, {"Average of Zip Code", type number}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Average of Zip Code", "Zip Code"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Zip Code]>=90000 and [Zip Code]< 93599 then "Southern California" else if [Zip Code]>=94600 and [Zip Code] <=96199 then "Northern California" else "undefined") in #"Added Custom"Please find the pbix as attached.
Regards,
Frank