Upload Excel File to Power Bi Service

Ability BI has been able to piece of work with Excel files since information technology was beginning introduced. Indeed, it was born from the analytic capabilities in Excel. Users tin connect directly to Excel files by using the Power BI service and nothing but a browser. However, depending on the content of the Excel file, and the method of connecting, the resulting products tin can be very different. In this mail service I volition attempt to clarify this beliefs. A subsequent post will detail the options available when working with Excel files in Power BI Desktop.

File Structure

Excel is a multi-purpose tool. Information technology contains all the building blocks of Power BI, and as such, information technology is an excellent Business Intelligence client. Excel files are also ofttimes used (much to my chagrin) every bit a data storage container, or equally a data transport medium. Understanding how the file is structured, and what you lot desire to practice with it is cardinal to making the right choice when combining it with Power BI.

Originally Excel files (workbooks) were collections of worksheets. Analysts could import data into those worksheets and so analyze them with the tools that Excel provided. Although Excel was never intended to exist a database, it'south ease of employ and familiarity led many people to brainstorm using it that was, and "spreadmarts" (spreadsheet information marts) chop-chop became a problem. The problems arose because the instant information was extracted from a source it became dried, and the fact that information technology was being stored in worksheets meant that it could exist edited (changing history) and became subject to the information size limitations of a worksheet.

To have advantage of Excel's analytic capabilities without being bailiwick to the issues involved in worksheet data storage, the data model was introduced, initially through PowerPivot. The information model is a "miniaturized" version of the SQL Server Analysis Services tabular engine that runs in Excel. This data model is read only, refreshable, and highly compressed which chiefly means that its merely data limitation is the amount of bachelor memory available on the machine running it. Importantly, this engine is the same engine that is used by Power BI – the advantages of which we'll explore presently.

Excel of course still needs to exist able to utilize worksheets and be Excel, so we tin can't just remove the worksheet capability (which incidentally is effectively what Ability BI Desktop is – Excel without worksheets). Therefore, today from a data perspective, Excel files can accept data in the information model, worksheets or both. From the Power BI service perspective, the important thing is whether the file contains a data model, as it treats the two cases differently.

Getting Excel Data

From the Power BI service, yous click the Go Data push, and and so the Get button in the Files tile. Yous are and then presented with one of 2 dialogs depending on whether you are using a personal workspace, or an app workspace.

Personal workspace

Connecting file-based information to a personal workspace

When importing into a personal workspace, there are iv possible data sources.

A local file is one that is stored on a file arrangement local to the motorcar being used. Selecting this option volition allow you to work with the Excel file stored there, just if the file is beingness used as a data source (data is in the worksheets), then a Data Gateway will exist required for any information refreshes. Ability BI will also connect to a file stored in OneDrive, either Personal or Business organisation (through role 365). Finally, the service can piece of work with files stored in whatsoever attainable SharePoint site (not simply Squad sites as the proper name would point).

App workspace

Connecting file-based data to an App workspace

When importing into an App workspace, there are 3 possible data sources. The Local File and SharePoint – Team Sites options are precisely the same as when importing into a personal workspace. The difference is the OneDrive – Workspace name pick replaces the two other OneDrive options. Choosing this option allows you lot to work with files stored in the "Group OneDrive". Since every App workspace is backed past an Office 365 or "Modern" grouping, it likewise has admission to the SharePoint site for that group. The "Group OneDrive" is the Documents library inside that SharePoint site. Therefore, choosing SharePoint – TeamSites and navigating to the Documents library will render the aforementioned results in a few more mouse clicks, merely also give access to all other document libraries within that site.

Connect vs Import

Once you navigate to the Excel file that you lot want to work with, you select it, and click connect. You volition and then be presented with two options for the file, Import or Connect.

This choice dictates how the file is brought into the Power BI service. The structure of the file determines exactly what is brought in to the service in both cases.

Connect

Clicking the Connect push allows Power BI to connect to and work with the Excel file in place. The workbook is displayed as an Excel workbook in total fidelity in the Ability BI interface using Excel Online. The file itself is shown in the Workbooks section in the Power BI interface, and it stands alone from other Ability BI elements (except that regions of it can exist pinned to a dashboard). Connecting to an Excel study will non create a Power BI Dataset, Report, or Dashboard. All operations, including refresh (see beneath) are controlled through the workbook.

At no point is the file moved, or "brought in" to the Ability BI service. If the file is beingness stored in SharePoint, or OneDrive, anything done to the file in the Power BI service will be visible to anyone with access to the file itself, whether they are a Power BI user or not. This includes refresh, which will exist discussed further below, just the important part to call back here is that if the data in the continued file is refreshed through the Power BI service, and information technology is being stored in SharePoint (or OneDrive), all users will be able to see updated data the next time that they open the file.

Connecting to an Excel file behaves the same way whether the file contains a information model or not, simply the file must comprise a information model in society to exist refreshed by the Power BI service.

Connected Excel file inside Ability BI

Import

Importing an Excel file behaves totally differently from connecting to information technology. When an Excel file is imported, information technology is treated as a data source to Power BI, and the assets inside that file are brought into the Power BI service. Subsequent changes to the source file are not immediately reflected within the Power BI service, but are retrieved through the refresh process.

The way that the assets are brought into the service depends very much on the structure of the file, specifically whether it contains a data model or not. If the file does not contain a information model, then Power BI will use the data contained in the Excel worksheets to construct a new i. This is similar to what happens when a CSV file is imported into the service. If the file does incorporate a data model, and so the worksheet data is imported, and that data model is brought into the service as-is. One important exception to this is if worksheet data uses the same query as an existing model, the worksheet data is ignored, and the data model is brought in as-is. This is of import because Excel'south Power Pivot editor tin be used to edit the model, creating calculated columns, calculated measures and relationships prior to import. The model that is automatically created when the file does not comprise a model has no editing capabilities.

When an Excel file with a data model is imported, the information model (imported or created) is added to datasets, and a link to the dataset is added to the default dashboard for the workspace. If no default dashboard exists, one will be created. A report can and then exist authored in the service. If the workbook contains whatever PowerView reports, these will be converted to native Power BI reports and added to the service as well. Any embedded 3D maps are not brought in.

Imported Excel File showing calculated measures

Refresh

Data refresh options, and beliefs depend on both the Become Data option (connect or import) and the construction of the Excel file.

Connected Workbooks

If the workbook is connected to the service, and it does not contain a data model, information technology cannot exist refreshed. This is true even if the worksheets in the workbook contain information from Power Query queries. This is the only scenario that does not back up refresh in any way.

If the workbook contains a data model refresh is supported. The interesting function is that refresh will be triggered not only for the data model itself, but for any worksheets that take Ability Queries equally a data source. Therefore, a workaround to the lack of refresh support for a worksheet with no data model is to add a blank data model.

For refresh to work, the information source must be bachelor to the Power BI service. This means that the source must exist bachelor in the cloud or registered on an available On-Premises Data Gateway.

The important thing to annotation almost connected workbooks is that the refresh options that are performed on them are permanent – refreshed information is stored with the workbook. This means that if the continued workbook is stored in SharePoint, or shared through OneDrive, updated information is available to all users with admission regardless of whether they are Power BI users.

Imported Workbooks

Refresh options for imported workbooks are slightly more than complicated. As mentioned above data is either imported from the worksheets, a data model imported into the service or both.

If data was imported from worksheets, then the Excel file is the data source from the standpoint of Power BI. If the file is stored in SharePoint or OneDrive, it volition automatically be refreshed every hour by default. This ways that changes to the underlying Excel file will be reflected back in the Power BI service within an hour. This feature tin can exist disabled, but it is non possible to modify to hourly schedule, nor precisely when it volition occur.

Refresh options for workbooks in OneDrive/SharePoint

If the file is stored on a file system, information technology can be scheduled more than granularly, simply you lot will need to connect to it through an On-Premises Data Gateway.

If the file independent a data model that was imported into the service, then the original source of information for that information model (the query) is what the Ability BI service will refresh from (Non the Excel file itself). In this case, the refresh options are the same as with most other Power BI data sources – Excel is taken out of the flick completely, and any changes to the source Excel file will not be reflected into the service. The exception to this is if the file had both a information model, and worksheet data that was imported.

In the example of an Excel with both a data model and worksheet data, both cases to a higher place volition use. The workbook is used as a data source for the table that was created past Power BI on import, and the original information model's source is updated independently. This means that changes to the worksheet data are reflected in the Power BI service when refreshed, but any model changes to the original Excel file are not. Both the OneDrive and regular refresh schedules are used for imported files of this blazon.

Refresh options for a combined data source

The post-obit table summarizes the refresh options available for file structure and connectedness type.

File Construction	Get Data option
	Connect	Import
Worksheet data	None	Refresh from worksheet
Data model only	Refresh from model source	Refresh from model source
Data model plus worksheet data	Refresh from model source and worksheet source	Refresh from model source and worksheet

Summary

Both Excel and Power BI are powerful tools in their own rights, and the decision to apply ane does not preclude using the other and in fact there are many skillful reasons for doing so. Bringing refreshability to Excel files stored in SharePoint is just one of them. Information technology is nonetheless important to understand how it all works in order to get the maximum impact.

smithsciusurturs1943.blogspot.com

Source: https://whitepages.unlimitedviz.com/2018/07/excel-files-power-bi-service/