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.
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: