Managing Power BI Data Sources: Tips and Techniques

by | Aug 22, 2018 | Data Analytics, Guest Posts

9 Min Read. |

Managing data coming from various sources lies at the core of Power BI. Whether you are exploring data, creating charts and dashboards, or asking questions, all data visualizations and answers you see, all of them get their underlying data from multiple data sources. Power-BI Excel Data Source is a broad and elaborate topic, which we will explain in detail in this discussion.

The best part of Power BI is that it supports a wide range of data sources and allows you to connect to different flat files, such as SQL database, and Azure cloud or even web platforms such as Facebook, Google Analytics, and Salesforce objects. Compatible Power-BI Excel Data Sources include data types like the Whole Number, Decimal Number, Currency, Date, True/False, and Text.

Power-BI and Dynamics are a very important part of our discussion on Power BI Data Sources. Power BI for Office 365 cloud service works in liaison with Microsoft Dynamics 365 to automatically refresh the Microsoft Dynamics 365 (online) data displayed. The Power BI Desktop or Microsoft Office Excel Power Query is more commonly used for authoring reports. Power BI is also useful for sharing dashboards and refreshing data from Microsoft Dynamics 365 (online), sales, marketing, and service personnel to improve workplace productivity.

In this discussion, I will provide an overview of the different types of data sources you can connect to from the Power BI service, right from your Power BI service site. My discussion will include:

  • Power BI Excel Data Source
  • Power BI and dynamics
  • Other data sources for Power-BI

You can click Get data and it shows all the available data connections. Moreover, Power-BI allows you to connect to different flat files, SQL database, and Azure cloud or even web platforms such as Facebook, Google Analytics, and Salesforce objects. It also includes an ODBC connection to connect to other ODBC data sources, which are not listed.

How to Import Excel Data into Power-BI

If you choose Import, any supported data in tables will be imported into a new dataset in Power BI. If you have any Power View sheets, those sheets will be re-created in Power BI as reports.

One of the best things about importing excel data into Power BI is that while you edit your workbook, the changes are saved and synchronized with the dataset in Power-BI, within a span of an hour. For more immediate gratification, simply click Publish again, and your changes are exported. This also includes any visualizations you have in reports and dashboards will be updated.

If you are using Excel 2016, you can also use Publish > Export. You may check out Publish to Power BI from Excel 2016 for more detailed information on this.

Power BI Excel Data Source: Connecting, Managing, and Viewing Excel in Power-BI

When you choose Connect, your workbook will appear in Power-BI just like it would in Excel Online. However, Power BI gives you some attractive features to help you locate elements from your worksheets right to your dashboards.

If you want to make any changes in the workbook in Power-BI, click Edit, and you can edit your workbook in Excel Online or open it in Excel. Any changes made are saved to the workbook on OneDrive.

When choosing this way, no dataset is created in Power-BI. Your workbook will appear in your Power BI workspace navigation pane under Reports. Connected workbooks have a special Excel icon. Choose this option if you only have data in worksheets, or you have ranges, PivotTables, and charts you want to pin to dashboards.

Power BI Excel Data Source: Getting data from Power BI Desktop files

business intelligence and reporting are enjoyable with Power BI Desktop. You might be connecting to distributed data sources, querying, and transforming data, modeling your data, and creating powerful and dynamic reports, Power-BI Desktop ensures faster and better completion of business intelligence tasks. Once you bring data into Power-BI Desktop and create a few reports, remember to get your saved file into the Power BI service.

Download Detailed Curriculum and Get Complimentary access to Orientation Session

Date: 13th Mar, 2021 (Saturday)
Time: 10:30 AM - 11:30 AM (IST/GMT +5:30)
  • This field is for validation purposes and should be left unchanged.

File Saving with Power BI:

Saving in Local: If you save your file to a local drive on your computer or another folder location in your organization, you can import your file or you can publish from Power BI Desktop to get its data and reports into Power BI. A copy of the file will be retained on your local drive. A new dataset is created in Power BI and data and the data model from the Power-BI Desktop files are loaded into the dataset. If your file has any reports, those will appear in your Power-BI site under Reports.

Saving in OneDrive:

  • OneDrive Business – If you are using OneDrive for Business, you must sign into it with the same account you sign into Power-BI with. This is the most effective way to keep your work in Power BI Desktop and your dataset, reports, and dashboards in Power BI in-sync. Since both Power BI and OneDrive are in the cloud, Power BI connects to your file on OneDrive about every hour. If any changes are found, your dataset, reports, and dashboards are automatically updated in Power-BI.
  • OneDrive Personal – If you save your files to your own OneDrive account, you will get the same benefits as you would with OneDrive for Business. The biggest difference is when you first connect to your file (using Get Data > Files > OneDrive – Personal) you will need to sign in to your OneDrive with your Microsoft account, which is usually different from what you use to sign in to Power-BI. When signing in with your OneDrive with your Microsoft account, be sure to select the Keep me signed in option. This way, Power BI will be able to connect to your file about every hour and make sure your dataset in Power BI is in-sync.
  • Saving to SharePoint Team-Sites –  Saving your Power-BI Desktop files to SharePoint – Team Sites is almost the same as saving to OneDrive for Business. The basic difference is how you connect to the file from Power BI. You can specify a URL or connect to the root folder.

Following are the available data sources in Power BI −

  • Flat Files
  • SQL Database
  • OData Feed
  • Blank Query
  • Azure Cloud platform
  • Online Services
  • Blank Query

Power-BI Data Sources includes other data sources such as Hadoop, Exchange, or Active Directory.

To get data in Power BI desktop, click the Get Data option on the main screen. It shows you the most common data sources first. Then, you need to click the More option to see a full list of available data sources.


When you click the More tab, you can see a new navigation window, where on the left side it shows a category of all available data sources. You also have an option to perform a search at the top.


Following are the various data sources listed −

  • All

Under this category, you can see all the available data sources under the Power-BI desktop.

  • File

When you click File, it shows you all flat file types supported in Power BI desktop. To connect to any file type, select the file type from the list and click Connect. You must provide the location of the file.

  • Database

When you click the Database option, it shows a list of all the database connections that you can connect to.


To connect to any database, select a Database type from the list as shown in the above screenshot. Click Connect.

You must pass Server name/ Username and password to connect. You can also connect via a direct SQL query using Advance options. You can also select Connectivity mode- Import or DirectQuery.

Note: You cannot combine import and DirectQuery mode in a single report.

Download Detailed Curriculum and Get Complimentary access to Orientation Session

Date: 13th Mar, 2021 (Saturday)
Time: 10:30 AM - 11:30 AM (IST/GMT +5:30)
  • This field is for validation purposes and should be left unchanged.

Import vs DirectQuery

DirectQuery option, another feature of Power-BI, limits the option of data manipulation and the data stays in the SQL database. DirectQuery is live and there is no need to schedule refresh as in the Import method.

Import method allows you to perform data transformation and manipulation. When you publish the data to PBI service, the limit is 1GB. It consumes and pushes data into Power BI Azure backend and data can be refreshed up to 8 times a day and a schedule can be set up for data refresh.


Advantages of Using DirectQuery

Using DirectQuery, you can build data visualizations on large datasets, which is not feasible to import in Power BI desktop.

  • DirectQuery does not apply any 1GB dataset limit.
  • With the use of DirectQuery, the report always shows current data.
  • Azure

Using the Azure option, you can connect to the database in the Azure cloud. Following screenshot shows the various options available under the Azure category.


Power-BI and Dynamics

Power BI for Office 365 cloud service works with Microsoft Dynamics 365 to provide a self-service analytics solution. Power BI automatically refreshes the Microsoft Dynamics 365 data displayed. With Power BI Desktop or Microsoft Office Excel Power Query for authoring reports and Power-BI for sharing dashboards and refreshing data from Microsoft Dynamics 365, sales, marketing, and service personnel in your organization have a powerful new way to work with Dynamics 365 data.

How to use Power-BI with Dynamics 365

The simplest way to start using Power-BI is to use Microsoft Dynamics content packs. A content pack is a collection of pre-configured, ready-made visuals and reports based on a specific data source. The content packs for Microsoft Dynamics allow you to quickly gain access to data through Power-BI and start interacting with it.

How to Embed Power BI visualizations on Personal Dashboards

Before you embed Power-BI visualizations on personal dashboards, ensure that the organization-wide setting is enabled.

Enable Power BI visualizations in the organization

Follow the steps below to Enable Power-BI visualizations in the organization:

  • Sign-in to Microsoft Dynamics 365 as a user with the system administrator security role.
  • Go to Settings > Administration > System Settings.
  • On the Reporting tab in the Allow Power BI visualization embedding option, select Yes to enable or No to disable.
  • Click OK.

How to Use Power-BI Desktop to connect directly to Microsoft Dynamics 365

You can connect to Microsoft Dynamics 365 (online) with Power BI Desktop to create custom Dynamics 365 reports and dashboards for use with the Power BI service.


  • Power BI service registration
  • Power-BI Desktop.
  • Microsoft Dynamics 365 (online) instance

Steps for Connecting to Dynamics 365:

  • Start Power BI Desktop.
  • From the Home tab, click Get Data, and then click More.
  • In the Get Data list, select Dynamics 365 Online.
  • Enter the Dynamics 365 (online) OData endpoint URL. It should look like this URL, where OrganizationName is the name of your Dynamics 365 (online) organization, and v8.1 is the version.
  • Click OK.
  • In the Access, an OData feed dialog click Organizational account and then click Connect.
  • The organization database entity tables appear in the Power-BI Desktop Navigator window. You can select both default and custom entities. For more information about creating reports with Power-BI Desktop, see Power BI Support: Report View in Power BI Desktop.

Now that you know a lot about Power-BI Data Sources and how to manage them, how it gets data from Excel Data Source, and how it works with Microsoft Dynamics 365, you may enroll for an advanced degree course in Power BI or Excel. It would hive your data analytics career, the right boost.

We offer one of the best-known Data Analytics Certification Training with Excel and Power BI. The course enables you to learn tools such as Advanced Excel, PowerBI, and SQL.  The live projects and intensive training program also empower you to come up with solutions for real-life problems.

Register for FREE Digital Marketing Orientation Class
Date: 03rd Mar, 2021 (Wed)
Time: 3:00 PM to 4:30 PM (IST/GMT +5:30)
  • This field is for validation purposes and should be left unchanged.
We are good people. We don't spam.

You May Also Like…

Linear Programming and its Uses

Linear Programming and its Uses

Optimization is the new need of the hour. Everything in this world revolves around the concept of optimization.  It...

An overview of Anomaly Detection

An overview of Anomaly Detection

Companies produce massive amounts of data every day. If this data is processed correctly, it can help the business to...


Submit a Comment

Your email address will not be published. Required fields are marked *