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