Create Column by region with zips

[mnarveson]

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

[v-frfei-msft]

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

[mnarveson]

@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

[mnarveson]

I created a new pbix with just the zipcode data on it. https://1drv.ms/u/s!AkAaFoxX6vm2t-pLIPgemSGR7jFutg

[Aron_Moore]

Depending 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-msft]

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

[mnarveson]

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 · Accepted solution]

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