Forum Discussion

zack's avatar
zack
Frequent Visitor
9 years ago
Solved

Census.gov API difficulty-- PowerBI Query doesn't display data

I need some help getting data returned by US Census.gov API (web data). I have registered for an API key, and have the proper syntax in the web URL, but when it gets to Power BI I can't use the data....
zack's avatar
zack
Frequent Visitor
9 years ago

ankitpatira Thank you. That doesn't work either. I referred to that in an earlier part of this thread. When I do as you suggest, it expands the column to one column of headers and field values. Which would be OK if I could transpose for a fixed number of columns (in this case 3 to correspond to the header columns).

 

Here's the link to the json file in question and query code i'm currently working on is below.  I can't figure out how to append or insert rows from subsequent sources. In the example below I'm simply manually trying to prove the concept. If I can get one source appended, then perhaps a loop statement will finish the job. 

let
    Source = Json.Document(Web.Contents("http://api.census.gov/data/2015/acs1/subject?get=S0101_C01_001E&for=county:021&in=state:37&key=MYCENSUSGOVKEYVALUE")),
    Table = Source{0},
    #"Converted to Table" = Table.FromColumns({Table}),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    
    Table1 = Source{1},
    #"Converted to Table1" = Table.FromColumns({Table1}),
    #"Transposed Table1" = Table.Transpose(#"Converted to Table1")
in
    #"Transposed Table1"
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Try 

Let
   Source = Json.Document(Web.Contents("http://api.census.gov/data/2015/acs1/subject?get=S0101_C01_001E&for=county:021&in=state:37&key=MYCENSUSGOVKEYVALUE")),
   Table = Table.FromRows(Source),
   PromotedHeaders = Table.PromoteHeaders(Table)
in
   PromotedHeaders

 

Note that 

Source = Json.Document(Web.Contents("http://api.census.gov/data/2015/acs1/subject?get=NAME,S0101_C01_001E&for=county:*&in=state:*&key=MYCENSUSGOVKEYVALUE"),

should return a single table of results for all Counties in all States if you need them.