Introduction
You do not need a data engineer to keep a report current. If your work already lives in Excel or Power BI, both tools can reach out to a public economic data API, pull the latest figures, and refresh on a schedule — all through Power Query, the data-loading engine shared by the two products. This guide shows how to load economic indicators directly, without a single line of script and without standing up a database.
The approach here is complementary to a scheduled automation tool like Power Automate. Power Query is best when the report itself is the destination; an automation flow is best when you want the data to land somewhere neutral that several reports can share. Many people end up using both.
Power Query in one paragraph
Power Query is the “Get Data” experience built into Excel and Power BI. It records the steps you take to fetch and clean data — connect to a source, expand a column, filter rows, change a type — and replays them every time you refresh. Because it records steps rather than values, a refresh re-runs the whole import against the live source. That is what makes a self-updating report possible.
Loading a JSON API into Excel
The cleanest public economic sources return JSON, and Power Query reads JSON natively. In Excel, go to Data → Get Data → From Other Sources → From Web, and paste an API URL. Using the World Bank API as an example, a request for a country’s GDP looks like this:
https://api.worldbank.org/v2/country/US/indicator/NY.GDP.MKTP.CD?format=json&per_page=100Power Query opens its editor and shows the raw response. Economic APIs often wrap their data in a header object and a data array, so you will typically click into the records, find the list of observations, and convert it To Table. From there you expand the columns you want — usually a date and a value — and set their types. When you close and load, the data lands in a worksheet.
💡 Tip: Do your filtering inside Power Query, not in the worksheet afterward. If you keep only the columns and rows you need before loading, refreshes stay fast and your file stays small. Trimming after the fact means you re-import everything on every refresh.
Parameterizing the source
Hard-coding one country or one series into a query works for a single chart, but you will quickly want to reuse the same query for several indicators. Power Query supports parameters for exactly this. Create a parameter for the indicator code, reference it inside the query’s URL, and you can swap indicators by changing one value rather than rebuilding the query.
For a small set of indicators, a common pattern is to keep a worksheet table listing the codes you track, then use it to generate one query per row. This mirrors the configuration-table idea from automation tools: the list of what you track lives in one obvious place, and adding a tracker is a one-row edit.
The same query in Power BI
Power BI uses the identical Power Query engine, so a query you build in Excel transfers almost directly. The practical difference is the refresh story. In Excel, a refresh happens when the file is open and someone clicks refresh or it runs on a timer. In Power BI, you publish the report to the Power BI service and configure scheduled refresh, which runs in the cloud on a cadence you set — daily is typical for economic data — without anyone opening the file.
This is the main reason teams graduate from Excel to Power BI for shared dashboards: the refresh runs whether or not a human is present, which is the same walk-away quality you want from any long-lived tracker.
📌 Note: Scheduled refresh in the Power BI service may require a data gateway and, for higher refresh frequencies, a Pro or Premium license. Check your licensing before promising stakeholders an hourly dashboard. For most economic indicators, a single daily refresh is plenty and stays within standard limits.
When the source needs a key
Some sources, including FRED, require an API key. Power Query can include the key as a query-string parameter, but a key embedded in a query is visible to anyone who can open the file or the published dataset. Treat that as a secret leak. The safer pattern is to keep keyed sources out of self-serve reports and instead let a scheduled automation flow fetch the keyed data into a neutral landing table (a SharePoint list or a file in OneDrive), then point Power Query at that table. The report consumes already-fetched data and never sees the key.
Strengths and limits
The strength of the Power Query route is that the report maintains itself and there is nothing to deploy. Anyone comfortable in Excel can build it, and the same work runs in Power BI when the audience grows. For indicators that update daily or monthly, this is often all the “pipeline” you will ever need.
The limits show up at scale and with secrets. Large historical pulls can make refreshes slow, keyed sources are awkward to secure inside a shared file, and complex multi-source joins are easier to maintain in a real transformation tool. When you hit those walls, move the heavy fetching into a scheduled job and let the report read the cleaned result — but most economic tracking never reaches that point.
Conclusion
Excel and Power BI can load live economic data on their own through Power Query, refresh on a schedule, and require no code and no backend. Build the query once against a JSON source, filter inside the query, parameterize the indicator so the list of what you track lives in one place, and let scheduled refresh keep it current. Keep API keys out of shared files by fetching keyed sources separately. Done this way, your report quietly stays up to date long after you have stopped thinking about it.