Excel PowerQuery & Census
October 22, 2022 5:09 AM Subscribe
I have a new gig where I'll need Census data early and often. Different surveys, different tables, different places, all.the.time.
I've really come around to liking PowerQuery as a tool to work with various data and get it into shape for automation/analysis.
How are there not more resources about these two things together?
If not PQ, is there another turnkey application that takes outputs from Census and makes them into usable table data?
(1) I'm still a pretty novice PQ user, but I'm open to any approach.
- Are there 'recipes' people use for in the editor?
- Are there other tools that integrate with PQ?
- Can you use M to set up processes in PQ that would take in Census?
(2) A lot of the portals for Census are great for finding what you need, but the outputs are formatted as reports, is there anything that generates more query-ready data formatting?
- Has anyone here used the API?
- I've seen a lot of posts and YT videos on analyzing a single output from Census, but hardly any on how to take multiple outputs from Census and process them together, what gives?
- I know there's table data for Census, but it seems like the portal for these is more manual and you take an entire type, but can't filter for region, etc
Has anyone here done this kind of work?
Has anyone seen good resources besides Census for working with Census data?
Given the ubiquity of both, how is it so hard to find stuff on Excel practice with Census?
(1) I'm still a pretty novice PQ user, but I'm open to any approach.
- Are there 'recipes' people use for in the editor?
- Are there other tools that integrate with PQ?
- Can you use M to set up processes in PQ that would take in Census?
(2) A lot of the portals for Census are great for finding what you need, but the outputs are formatted as reports, is there anything that generates more query-ready data formatting?
- Has anyone here used the API?
- I've seen a lot of posts and YT videos on analyzing a single output from Census, but hardly any on how to take multiple outputs from Census and process them together, what gives?
- I know there's table data for Census, but it seems like the portal for these is more manual and you take an entire type, but can't filter for region, etc
Has anyone here done this kind of work?
Has anyone seen good resources besides Census for working with Census data?
Given the ubiquity of both, how is it so hard to find stuff on Excel practice with Census?
Best answer: Yeah, I think that the reason is that most people who do significant census-based data analysis don't use Excel. They are either using open source tools like R or Python, which have a huge ecosystem of tools to deal with census data, or they are using closed source tools like SAS, that ALSO have census toolkits. Excel also lags far behind tools like R and Python when working with the geospatial components of census data.
There is some training from the Census Bureau on using Excel which may be helpful to get you started.
posted by rockindata at 6:51 AM on October 22, 2022
There is some training from the Census Bureau on using Excel which may be helpful to get you started.
posted by rockindata at 6:51 AM on October 22, 2022
I agree with the above that there are good reasons why the most experienced practitioners use R/Python Pandas/SAS/etc. With that said, I applaud you for diving into PowerQuery. If you are going to keep doing this in Excel, PowerQuery is the "right" way to do this.
posted by mmascolino at 6:58 AM on October 22, 2022
posted by mmascolino at 6:58 AM on October 22, 2022
Are there other tools that integrate with PQ?
Microsoft’s Power BI is Excel on steroids and utilizes Power Query as an optional front-end ETL lite, but also allows you to use it to transform data in Data Flows in the workspace to improve performance. The industry is shifting toward using Power BI being a primary reporting tool, integrating it with Azure Synapse Analytics and other MSBI Stack essentials.
That being said, if you can use literally anything else to wrangle your data from the Census APIs, such as Python as others have suggested, I’d say do that.
posted by Cyber666 at 12:04 PM on October 22, 2022
Microsoft’s Power BI is Excel on steroids and utilizes Power Query as an optional front-end ETL lite, but also allows you to use it to transform data in Data Flows in the workspace to improve performance. The industry is shifting toward using Power BI being a primary reporting tool, integrating it with Azure Synapse Analytics and other MSBI Stack essentials.
That being said, if you can use literally anything else to wrangle your data from the Census APIs, such as Python as others have suggested, I’d say do that.
posted by Cyber666 at 12:04 PM on October 22, 2022
I agree with others that the Census -> Excel pipeline is not the typical approach. Excel isn't the tool of choice for many because it can't handle the more complex analyses, nor the larger datasets. The "pros" are typically accessing and manipulating census data packages available in programming languages like SAS, Python, and/or R. Python and R are free and open source.
Depending on which data product(s) you are using, you might find accessing the data through IPUMS to be easier.
That being said, novices generally aren't going to find this process to be easy. Therefore, I suggest reaching out to the Census Bureau directly for advice. Seriously! The Census Bureau wants people / orgs to use their data (correctly)!
posted by oceano at 8:16 PM on October 23, 2022
Depending on which data product(s) you are using, you might find accessing the data through IPUMS to be easier.
That being said, novices generally aren't going to find this process to be easy. Therefore, I suggest reaching out to the Census Bureau directly for advice. Seriously! The Census Bureau wants people / orgs to use their data (correctly)!
posted by oceano at 8:16 PM on October 23, 2022
This thread is closed to new comments.
posted by jilloftrades at 6:12 AM on October 22, 2022 [1 favorite]