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 ,

 

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

  • Aron_Moore's avatar
    Aron_Moore
    Icon for Solution Specialist rankSolution Specialist
    7 years ago

    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'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?