topic Re: Census.gov API difficulty-- PowerBI Query doesn't display data in Power Query

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

zack — Wed, 21 Sep 2016 16:53:21 GMT

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?

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

Anonymous — Wed, 21 Sep 2016 17:09:30 GMT

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

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

zack — Wed, 21 Sep 2016 17:15:31 GMT

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

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

Anonymous — Wed, 21 Sep 2016 17:18:34 GMT

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.

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

zack — Wed, 21 Sep 2016 17:43:48 GMT

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.

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

Anonymous — Wed, 21 Sep 2016 18:12:36 GMT

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

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

zack — Wed, 21 Sep 2016 20:01:21 GMT

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:

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

ankitpatira — Thu, 22 Sep 2016 01:06:15 GMT

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.

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

zack — Thu, 22 Sep 2016 12:31:01 GMT

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"

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

Anonymous — Fri, 23 Sep 2016 03:45:38 GMT

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.

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

zack — Fri, 23 Sep 2016 12:58:46 GMT

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