Forum Discussion
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...
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
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
Community Support
Community SupportHi 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