Forum Discussion

mnarveson's avatar
mnarveson
Regular Visitor
7 years ago
Solved

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 t...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
mnarveson
Regular Visitor
7 years ago

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's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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