Edit

Share via


Where to get data

Getting data from available data sources is usually the first encounter you have with Power Query. This article provides basic steps for getting data from each of the Microsoft products that include Power Query.

Note

Each of these Power Query get data experiences contain different feature sets. More information: Where can you use Power Query?

Get data in Power BI Desktop

The procedures for where to start getting data in Power BI Desktop are described in Data sources in Power BI Desktop.

In Power BI Desktop, you can also directly select an Excel worksheet, a Power BI semantic model, a SQL server database, or Dataverse data without using the Get data option. You can also enter data directly in a table, or select from a data source that was recently used.

Screenshot of the Data field in the Home ribbon of Power BI Desktop.

Get data in Excel (Windows or Mac)

To get data in Excel for Windows or Excel for Mac:

  1. From the Data ribbon, select Get Data.

  2. Scroll through the category selections in the get data context menu, and select the connector you want to use.

You're then asked to fill out the required information for you to access the data. Go to the individual connector articles for more information about this required information.

In Excel for Windows, you can also directly select Text/CSV, Web, and Excel worksheet data without using the Get Data option. You can also select from a data source that was recently used and from existing connections or tables.

Note

Not all Excel versions support all of the same Power Query connectors. For a complete list of the Power Query connectors supported by all versions of Excel for Windows and Excel for Mac, go to Power Query data sources in Excel versions.

Get data in Power BI service

To get data in Power BI service:

  1. On the left side of Power BI service, select Workspaces (but not My Workspace).

  2. From the Workspace context menu, select the workspace you want to use.

  3. From the workspace (in this example, TestWorkspace01), select the context menu next to New.

  4. From the New context menu, select Dataflow.

    Screenshot with text workspace open, the context menu for new open, and dataflow emphasized.

  5. From Define new tables, select Add new tables.

  6. In the Choose data source page, use Search to search for the name of the connector, or select View more on the right hand side the connector to see a list of all the connectors available in Power BI service.

    Screenshot of the Choose data source page with the search box and the view more selection emphasized.

  7. If you choose to view more connectors, you can still use Search to search for the name of the connector, or choose a category to see a list of connectors associated with that category.

    Screenshot of the Choose data source page displayed after selecting view more, including the categories at the top and then the list of connectors.

Get data in Power Apps

There are several places in Power Apps where you can get data, either by importing data to a table or by creating a dataflow.

To import data to a new table in Power Apps:

  1. On the left side of Power Apps, select Dataverse > Tables.

  2. In the Tables pane, at the top left, select Import > Import data.

    Screenshot with Dataverse tables open, the import context menu open, with the import data option emphasized.

    You can also choose to get data directly from an Excel worksheet without using the Import data option.

    Image with Dataverse tables open, the import context menu open, with the import data from Excel option emphasized.

  3. In the Choose data source page, use Search to search for the name of the connector, or select View more on the right hand side the connector to see a list of all the connectors available in Power BI service.

    Screenshot of the Power Apps Choose data source page with the search box and the view more selection emphasized.

  4. If you choose to view more connectors, you can still use Search to search for the name of the connector, or choose a category to see a list of connectors associated with that category.

    Screenshot of the Power Apps Choose data source page displayed after selecting view more, including the categories at the top and then the list of connectors.

To import data to an existing table in Power Apps:

  1. On the left side of Power Apps, select Dataverse > Tables.

  2. In the Tables pane, either:

    • Select a table in the Tables pane that you want to import data to, and then select Import > Import data.

      Screenshot with Dataverse tables open, a table selected, the import context menu open, with the import data option emphasized.

    • Open the table to its individual pane, and then select Import > Import data.

      Screenshot with Dataverse tables open, an individual table open in a pane, the import context menu open, with the import data option emphasized.

    In either case, you can also choose to get data from an Excel worksheet without using the Import data option.

    Image with Dataverse tables open, and individual table open in a pane,the import context menu open, with the import data from Excel option emphasized.

  3. Select the connector from the list of data sources.

To get data in Power Apps when creating a dataflow:

  1. On the left side of Power Apps, select Dataverse > Dataflows.

  2. If a dataflow already exists:

    1. Double-click on the dataflow.
    2. From the Power Query editor, select Get data.
    3. Select the connector from the list of data sources.
  3. If no dataflow exists and you want to create a new dataflow:

    1. Select New dataflow.

    2. In the New dataflow dialog box, enter a name for your new dataflow.

      Screenshot of the New dataflow dialog box, with the name text box filled in.

    3. Select Create.

    4. Select the connector from the list of data sources.

Get data in Dynamics 365 Customer Insights

To get data in Customer Insights:

  1. On the left side of Customer Insights, select Data > Data sources.

    Screenshot with Data sources open, and Add data source emphasized.

  2. In the Data sources pane, select Add data source.

  3. In Choose your import method, choose Microsoft Power Query.

  4. In Save data source as, enter a name for your data source.

    Screenshot with Choose your import method open, with Microsoft Power Query selected, and MyNewDataSource set for Save data source as.

  5. Select Next.

  6. In the Choose data source page, use Search to search for the name of the connector, or select View more on the right hand side the connector to see a list of all the connectors available in Power BI service.

    Screenshot of the Customer Insights Choose data source page with the search box and the view more selection emphasized.

  7. If you choose to view more connectors, you can still use Search to search for the name of the connector, or choose a category to see a list of connectors associated with that category.

    Screenshot of the Customer Insights Choose data source page displayed after selecting view more, including the categories at the top and then the list of connectors.

Get data from Data Factory in Microsoft Fabric (Preview)

To get data in Data Factory:

  1. On the left side of Data Factory, select Workspaces (but not My Workspace).

  2. From your Data Factory workspace, select New > Dataflow Gen2 (Preview) to create a new dataflow.

    Screenshot showing the workspace where you choose to create a new dataflow.

  3. In Power Query, either select Get data in the ribbon or select Get data from another source in the current view.

    Screenshot showing the Power Query workspace with the Get data option emphasized.

  4. In the Choose data source page, use Search to search for the name of the connector, or select View more on the right hand side the connector to see a list of all the connectors available in Power BI service.

    Screenshot of the Data Factory Choose data source page with the search box and the view more selection emphasized.

  5. If you choose to view more connectors, you can still use Search to search for the name of the connector, or choose a category to see a list of connectors associated with that category.

    Screenshot of the Data Factory Choose data source page displayed after selecting view more, including the categories at the top and then the list of connectors.

Get data in Analysis Services

With Analysis Services, a solution is developed in Visual Studio with the Analysis Services projects extension. The solution is then deployed to SQL Server Analysis Services, Azure Analysis Services, or a Power BI Premium workspace.

The Analysis Services documentation contains the following information that describes the process for getting data:

Get data in Power Automate

There are a couple of places in Power Automate where you can get data, either by importing tables or in process adviser.

To get data by importing tables:

  1. On the left side of Power Automate, select Data > Tables. At this point, a new tab with Power Apps opens in your browser.

  2. In the Power Apps tab, follow the instructions for importing data to either a new table or to an existing table in the Power Apps section.

For information about how to get data in process advisor, go to Connect to a data source.

Get data in Azure Data Factory

Azure Data Factory doesn't use the same Power Query get data interface as the other apps described in this article. Instead, Azure Data Factory uses its own built-in functionality to import data.

However, Azure Data Factory does use Power Query to transform data in data wrangling. The following Azure Data Factory articles describe how to use Power Query for data wrangling:

Get data in SQL Server Integration Services

SQL Server Integration Services (SSIS) doesn't use the same Power Query get data interface as the other apps described in this article. Instead, SSIS uses its own built-in functionality to import data.

For information on installing, configuring, and using the Power Query Source for SSIS, go to Power Query Source.