With Microsoft Power Query for Excel, you can import data from a large collection of public data sources including Wikipedia tables, a subset of Windows Azure Marketplace, and a subset of Data.gov. For a list of public data sources accessible from Power Query, see Public data sources accessible from Power Query.
Note Public data sources are available for free, and sources from Windows Azure Marketplace do not need any additional account login.
I want to
Search for public data
You can search for data from a large collection of public data sources. For example, you can search from a collection of “S&P 500” data sources from Wikipedia.
- In the POWER QUERY ribbon tab, click Online Search.
- In the Online Search pane, enter a search term such as “S&P 500”.
- Press Enter or click the search icon ( ).
- To navigate to a data source page, click a page number or Next.
For a list of public data sources accessible from Power Query, see Public data sources accessible from Power Query.
Use the Search ribbon tab
With the SEARCH ribbon tab, you can fine-tune your search criteria. You can set a Scope to filter your search by range of data sources or refine a search filter. Refine inserts a search filter into the Online Search box.
Scope – Filters your search by range of data sources.
- Public – Public data sources including Wikipedia tables, a subset of Windows Azure Marketplace, and a subset of Data.gov. For a list of public data sources accessible from Power Query, see Public data sources accessible from Power Query.
- For Power Query 2.10:
- My Shared – Range of data sources include my shared queries.
- Organization – Range of data sources include queries shared within the enterprise.
- All – Apply a search term for all scoped data sources.
Refine – Filters your search based on a query or table attribute. For example, to filter where the term “Index” is in the query name, the search filter is name:(Index).
|Query or table attribute
||Syntax and Example
||Filter your search based on query and table names.
||Filter your search based on query and table description.
||Filter your search to only show results from specific people or web pages.
||Filter your search based on the underlying data source name
Filter your search based on when the query was last modified. The date range options:
Today, Yesterday, This Week, Last Week, This Month, Last Month, This Year, Last Year
Example: lastmodifieddate:this week
||Filter your search based on the column names in the data source.
Example: columnname:(Company Name)
Import a public data source
- In the Online Search pane, hover over data source summary items to render a data source preview.
- Point your mouse or click on any of the data sources in the search result to see a preview of the data source.
The preview fly out displays a snapshot of the selected public data source, the columns in the dataset, last modified timestamp, and the source/owner of the public data source, You can click on the column name in the preview dialog box to jump to the respective data column in the preview. Additionally, the specified keyword values are highlighted in the preview fly out screen.
In the preview fly out screen:
- To insert the data source into the workbook, click ADD TO WORKSHEET.
- To refine the data source query, click FILTER & SHAPE.
- To navigate to the source page of the data source, click the Data Source link.
Public data sources accessible from Power Query
Note Public data is provided by a third party and you should refer to the third party license regarding use of data. Public data sources are managed by a third party and are subject to change.
You can search from the following data sources:
- Windows Azure Marketplace: free sources, not requiring to be logged-in
- Including some data from the Bureau of Economic Analysis
- The World Bank
- MCH Strategic Data: samples
- Dun & Bradstreet: samples about Seattle companies