Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
zack
Frequent Visitor

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

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.

Census_data_browser.PNGCensus_data_pbiquery.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any suggestions on what to do?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

10 REPLIES 10
ankitpatira
Community Champion
Community Champion

@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"
Anonymous
Not applicable

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.

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

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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.

click_on_list.PNGexpand_column.PNG

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors