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

[zack]

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. I'm sure the problem rests with me.

Here are screen shots of what it looks like both in my browser and in Power BI's query editor.

Any suggestions on what to do?

[ankitpatira]

@zack How about when you're presented with lists for the first time -> right click -> convert to table (or highlight column and their should be option on top left hand side somewhere that says convert to table). See if that helps and it converts it to table for all.

[zack]

@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 · Accepted solution]

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=MYC..."),

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

[zack]

Thank you! That did it. Much appreciated. I restricted the counties until i could figure out the formatting problem.

[Seth_C_Bauer]

@zack The "List" value is highlighted, which is an indicator to me that you should be able to click on that and choose the "nested" values associated with that list. These values then become your table...

[zack]

Thanks for the reply. This is true. But seems crazy to have to manually click all rows to create that many different queries/tables.

[Seth_C_Bauer]

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

[zack]

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.

[Seth_C_Bauer]

@zack This post looks very similiar, and has a solution utilizing "M"

[zack]

@Seth_C_Bauer 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.