Drilldown Choropleth - State to Zip (or City)

[alokthakkar]

Hi,

I have 2 questions.

1. I downloaded the below 2 Json files and tried to host locally and map it. But it is not working. How, can I host thee Json file for City & County and use it.

https://uat.gramener.com/static/c3/US_adm2.json

https://uat.gramener.com/static/c3/US_adm1.json

2. I want to provide further drill down to City and zip code. Where can I file Json file for the same. It limit the ability to a great visual created. It would be great help If I get quick answer on the same.

Thanks,

Alok

[v-ljerr-msft]

Hi @alokthakkar,

If I understand you correctly, you should be able to use the M query below to import USA_adm2 json with Power BI Desktop(Get Data > Blank Query > Enter the Query). 

let
    Source = Json.Document(File.Contents("C:\Users\username\Desktop\US_adm2.json")),
    objects = Source[objects],
    USA_adm2 = objects[USA_adm2],
    geometries = USA_adm2[geometries],
    #"Converted to Table" = Table.FromList(geometries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1][properties]),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"ID_0", "ISO", "NAME_0", "ID_1", "NAME_1", "ID_2", "NAME_2", "NL_NAME_2", "VARNAME_2", "TYPE_2", "ENGTYPE_2"}, {"Custom.ID_0", "Custom.ISO", "Custom.NAME_0", "Custom.ID_1", "Custom.NAME_1", "Custom.ID_2", "Custom.NAME_2", "Custom.NL_NAME_2", "Custom.VARNAME_2", "Custom.TYPE_2", "Custom.ENGTYPE_2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Column1"})
in
    #"Removed Columns"

Regards

[alokthakkar]

Hi,

Thanks for your response. I am new to Power BI and learning. 🙂  I followed the steps below and getting error. Not sure if I am missing some step here. The only point I can load the table (not sure what will I do with that table) when I use below syntax. . It would be great help if you could guide further in the same.

Working 

= Json.Document(File.Contents("C:\AlokThakkar\US_adm2.json"))

Error # 1

DataFormat.Error: We found extra characters at the end of JSON input. Details:     Value=     Position=0

 Also, How do I use the City and zip from US_adm2.json. I believe your answer is for my first question only.

Thanks in advance for your guidance. Appreciate it.

Thanks,

Alok