Search public data

With Microsoft Power Query for Excel, you can import data from a large collection of public data sources including Wikipedia tables, a subset of Microsoft 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 Microsoft 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.

  1. In the POWER QUERY ribbon tab, click Online Search.
  2. In the Online Search pane, enter a search term such as “S&P 500”.
  3. Press Enter or click the search icon (Search icon ).
  4. 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 Microsoft 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 Description Syntax and Example
Name Filter your search based on query and table names.

name:(name)

Example: name:(Index)

Description Filter your search based on query and table description.

description:(description)

Example: description:(GDP)

From Filter your search to only show results from specific people or web pages. from:(email)
Data Source Filter your search based on the underlying data source name

datasource:(source)

Example: datasource:(AdventureWorks2012)

Date Range

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

lastmodifieddate:{date range}

Example: lastmodifieddate:this week

Column Name Filter your search based on the column names in the data source.

columnname:(name)

Example: columnname:(Company Name)

Import a public data source

  1. In the Online Search pane, hover over data source summary items to render a data source preview.
  2. Point your mouse or click on any of the data sources in the search result to see a preview of the data source.

Preview of the shared query

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:

Public sources

Data source Number of sources
Wikipedia (all)
Microsoft Azure Marketplace
(Includes some data from the Bureau of Economic Analysis)
11
Data.gov 2197
The World Bank 68
HealthData.gov 500
S&P 500 499
United States Census 2010 321

Commercial data

  • MCH Strategic Data: 3 samples
  • Dun & Bradstreet: samples about Seattle companies

Detailed list

Data source Number
Bureau of Economic Analysis 4
Bureau of Engraving and Printing 2
Bureau of Labor Statistics 1
City of Barcelona 1
Dun & Bradstreet 3
Data.gov 252
Department of Education 2
Department of Education, EDFacts 9
Department of Energy 4
Department of Health & Human Services 244
Department of State 3
Department of Veterans Affairs 24
Development Credit Authority 2
Development Credit Authority 4
Election Assistance Commission 5
Energy Information Administration 1
Environmental Protection Agency 1450
European Environment Agency 1
Export-Import Bank of the US 14
Federal Acquisition Service 5
Federal Deposit Insurance Corporation 1
Federal Election Commission 3
Federal Highway Administration 153
Food and Nutrition Service 4
General Services Administration 4
Google Finance 498
Government of United Kingdom 1
GTW Holdings 1
Internal Revenue Service 5
MCH Strategic Data 3
National Cemetery Administration 57
National Center for Education Statistics 15
National Center for Education Statistics 7
National Science Foundation 4
National Security Division 1
New York State 48
Office of Citizen Services and Innovative Technologies 15
Office of Government Ethics 1
Office of Government-wide Policy 5
Office of Management and Budget 4
Office of Navajo and Hopi Indian Relocation 1
Office of Personnel Management 19
Office of Public Records 1
Office of the Assistant Secretary for Policy 1
Office of the Chief People Officer 1
Office of the Federal Register 1
PricingKernel LLP 1
Public Buildings Service 3
Securities and Exchange Commission 3
Social Security Administration 28
State of Colorado 2
State of Hawaii 50
State of Illinois 129
State of Maryland 36
State of Missouri 2
State of Oklahoma 6
State of Oregon 4
State of Washington 5
The World Bank 68
United States Coast Guard 6
University of Pittsburgh 2
US Census Bureau 321
US Equal Employment Opportunity Commission 26
US Trade Representative 3
Veterans Benefits Administration 56
Veterans Health Administration 2
World Bank Data Indicators 8510
Yahoo Finance 1
 
 
Applies to:
Excel 2013, Excel 2010