Forum Discussion
Census.gov API difficulty-- PowerBI Query doesn't display data
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
PromotedHeadersNote 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.
Thanks for the reply. This is true. But seems crazy to have to manually click all rows to create that many different queries/tables.
zack You shouldn't need to... once you click one cell, the columns you choose will be applied all the way down the entire data set and a new table will be formed with the requested info. It should be a one time operation.
Ah. Thanks. That doesn't happen for me. I'm in PowerBI Desktop. When I click the hyperlinked "List" in the List column it expands the three fields and all the other records go away. I can get the query to display an expandable column, but the problem with that is that the data is organized in one long column. And I can't tell it to transpose x columns (to equal header column count). 2 screen shots to illustrate both issues.
Anonymous thanks. you're right that it's similar, but not enough to solve my problem. For example, I get an error when trying to add the index column. It seems I have to covert to table, and transpose rows before i can add an index column. But if I knew what i was talking about this discussion would not be takign place. :smileyfrustrated: