Forum Discussion
Create Column by region with zips
I am trying to create a new column by zip code. I need to serperate them by region. How can I put the range in like
IF [Zip] = 90000 to 93599 then "Southern California"
IF [Zip] = 93600 to 96199 then "Northern California"
I am trying to use DAX and have to create regions for the whole US
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
8 Replies
v-frfei-msftCommunity Support
Hi mnarveson ,
To create a calculated column as below.
Column = IF ( [ZIP] >= 90000 && [ZIP] <= 93599, "Southern California", IF ( [ZIP] >= 94600 && [ZIP] <= 96199, "Northern California", BLANK () ) )
Regards,
Frank
v-frfei-msft Thanks for the response. It looked like your solution would work. However, I get an error on the "IF" saying that name is not recognized. I have no syntax errors before I run it too.
- v-frfei-msft
Hi mnarveson ,
Could you please share your pbix or sample data to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.
Regards,
Frank
