Forum Discussion
Same City Names in different States
I have attached a sample file. Column A contains the cities. Some of them have same city names but their state names are different(column M). I want to add State abbreviation (Column N) to only those...
A Power Query option attached
let
Source = Table,
GroupedRows = Table.Group(Source, {"PHYSICAL CITY"},
{"DATA", each
Table.AddColumn(_, "PHYSICAL CITY-STATE", (o)=>
if Table.RowCount(_) > 1 then Text.Combine({o[PHYSICAL CITY], o[PHYSICAL STATE]}, "-") else null
),
type table
}
),
CombineTables = Table.Combine(GroupedRows[DATA])
in
CombineTables- Thank you so much. Appreciate your help.
Also kindly let me know if there is anyway, the sheet can fetch Lattitude, Longitude, County and County FIPS from any external source like Google Maps. The file contains the same Lat, Long, County and County info for all the duplicate cities irrespective of the states they are in. Just wondering if this is possible, rather than editing them manually.- I was able to figure this out using Google's Geo Coding. Please ignore the above message. Thanks
