Skip to main content

Module 2: Connect Power BI to Additional Data Sources

Azure SQL Database​

  1. Need a Azure tenant, subscription, and resource group and then a database and server
  2. Get the server name, database name, username, and password
  3. In Power BI Desktop, select Get Data > Azure > Azure SQL Database
  4. Enter the server and database names, then select DirectQuery or Import
  5. Enter the username and password, then select Connect
  6. Select the tables and views to load into Power BI
  7. Transform and model the data as needed

SharePoint Online List​

  1. In Power BI Desktop, select Get Data > More > Online Services > SharePoint Online List
  2. Enter the SharePoint site URL (not the list URL)
  3. Select OK, then sign in with your Microsoft 365 credentials if prompted
  4. Select the list to load into Power BI
  5. Transform and model the data as needed Note: You may need to enable the SharePoint Online List connector in Power BI options :(

Sharepoint Document Library​

  1. In Power BI Desktop, select Get Data > More > File > SharePoint Folder
  2. Enter the SharePoint site URL (not the document library URL) Starts with https:// ;)
  3. Select OK, then sign in with your Microsoft 365 credentials if prompted
  4. Select the files to load into Power BI
  5. Transform and model the data as needed

Connecting to Excel files in SharePoint​

  1. In Power BI Desktop, select Get Data > More > File > SharePoint Folder
  2. Enter the SharePoint site URL (not the document library URL)
  3. Select OK, then sign in.
  4. In the Navigator, select the Excel file to load into Power BI
  5. Transform and model the data as needed

Connecting to third-party data sources​

  1. In Power BI Desktop, select Get Data > More
  2. Browse or search for the desired connector (e.g., Salesforce, Google Analytics, etc.)
  3. Select the connector, then select Connect
  4. Enter the required connection details (e.g., URL, credentials, API key)
  5. Select the tables or data to load into Power BI
  6. Transform and model the data as needed

Connecting to a Dataverse environment​

  1. In Power BI Desktop, select Get Data > More
  2. Browse or search for the Dataverse connector
  3. Select the connector, then select Connect
  4. Enter the required connection details (e.g., environment URL, credentials)
  5. Select the tables or data to load into Power BI
  6. Transform and model the data as needed

Connecting to text and CSV Data Sources​

  1. In Power BI Desktop, select Get Data > Text/CSV
  2. Browse to the location of the text or CSV file, then select Open
  3. In the preview window, select the delimiter and other options as needed
  4. Select Load to import the data into Power BI
  5. Transform and model the data as needed

Connecting to Google Sheets​

  1. In Power BI Desktop, select Get Data > More > Online Services > Google Sheets
  2. Sign in with your Google account and authorize Power BI to access your Google Sheets
  3. This gives you a warning that this is a third-party connector and that you should only proceed if you trust the source.
  4. Select the Google Sheet to load into Power BI
  5. Sheet1 is the entire sheet, try to select a table or named range if possible.
  6. Transform the data as needed

Odata Feed​

Azure DevOps, Dynamics 365, SharePoint, and many other services expose OData feeds that you can connect to from Power BI. :)

  1. In Power BI Desktop, select Get Data > More > Other > OData Feed
  2. Enter the OData feed URL, then select OK
  3. If prompted, enter your credentials to access the OData feed
  4. Select the tables or data to load into Power BI
  5. You may need to select specific URLs and use the OData options to get the data you want.
  6. Transform and model the data as needed

Additional Ways to get data into Power BI

Entering Data Manually​

  1. In Power BI Desktop, select Home > Enter Data
  2. In the Create Table dialog, enter the data manually or copy and paste from another source
  3. Define the column names and data types as needed
  4. Select Load to import the data into Power BI
  5. Transform and model the data as needed

TIP: You can also use the Enter Data option to create a table from clipboard data by copying data from another application (e.g., Excel) and then selecting Paste in the Create Table dialog. The only way to edit this data is to edit the advanced query in Power Query.

Using the R programming language​

  1. In Power BI Desktop, select Get Data > More > Other > R Script
  2. Enter the R script that retrieves or generates the data, then select OK
  3. If prompted, install the R packages required by the script
  4. Select the tables or data to load into Power BI
  5. Transform and model the data as needed

Create a Date Table​

  1. In Power BI Desktop, select Modeling > New Table
  2. Enter the DAX formula to generate a date table, for example: 
    DateTable = CALENDAR(MIN(Sales[OrderDate]), MAX(Sales[OrderDate]))
  3. Define the column names and data types as needed - convert to a table, change the data type to date.
  4. Change the column name to Date.
  5. Add additional columns for year, month, quarter, etc. using DAX formulas
  6. Select Close and Apply to import the data into Power BI
  7. Transform and model the data as needed

Create table of last refreshed date​

  1. In Power BI Desktop, select Modeling > New Table
  2. Enter the DAX formula to create a table with the last refreshed date, for example: 
    LastRefreshed = DATATABLE("Last Refreshed", STRING, { {FORMAT(NOW(), "MMMM DD, YYYY HH:MM AM/PM")} })
  3. Select Close and Apply to import the data into Power BI
  4. Use this table in your reports to display the last refreshed date
  5. Use this table in a card visual to show the last refreshed date on your report ALOT.

Creating tables using DAX​

  1. In Power BI Desktop, select Modeling > New Table
  2. Enter the DAX formula to create a new table based on existing data, for example: 
    TopCustomers = TOPN(10, Customers, Customers[TotalSales], DESC)
  3. Define the column names and data types as needed
  4. Select Close and Apply to import the data into Power BI
  5. Transform and model the data as needed