Importing Full File Content in Excel and Power BI

Power BI Desktop can analyze larger workbooks, however, the larger the workbook, the longer it will take for Power BI Desktop to process and import it. The video also offers a workaround for loading or importing Excel files from shared OneDrive for work or school folders or from Microsoft 365 group folders: using the URL of the specific Excel file and inputting it into the Web data source in Power BI Desktop.

With Power BI Desktop, once the file is imported, the workbook is then converted into a Power BI Desktop file (.pbix) and Power BI Desktop prepares a report based on the workbook's content. This process is a one-time event and there is no dependence on the original Excel workbook after the file has been created. This ensures that modifications, saving, and sharing do not affect the original workbook.

Deep Dive into Importing Workbooks

Importing workbooks into Power BI Desktop is a powerful way to leverage Excel's flexible data manipulation capacities with Power BI's robust reporting and visualization tools. The imported elements, typically referred to as objects, include Power Query queries, Power Pivot external data connections, worksheet tables linked to the data model, and data model calculated columns, measures, KPIs, data categories, and relationships.

• Power Query queries are converted to queries in Power BI Desktop. If there are query groups defined in the Excel Workbook, these are replicated in Power BI Desktop.
• Power Pivot external data connections are converted into queries in Power BI Desktop.
• Linked tables or currently in use workbook tables are imported into the Power BI Desktop file, or a connection to the original Excel workbook can be preserved, retrieving the latest content with each refresh.
• Data model calculated columns, measures, KPIs, data categories, and relationships convert to equivalent objects in Power BI Desktop.

However, there are a few limitations to the import process, including support for external connections to SQL Server Analysis Services tabular models, hierarchies, binary data columns, and named ranges using From Table in Power Query, or using Excel.CurrentWorkbook in M. Furthermore, PowerPivot external connections to SQL Server Reporting Services (SSRS) are not currently supported.

Learn about How to Import Entire File Content in Excel or Power BI

This video outlines the process of importing entire file contents into Microsoft Excel Power Query or Power BI. It serves as a tutorial for users of either of these applications, and particularly useful as a guide for beginners to these platforms.

The process discussed in the video touches on various aspects including the actual import of an Excel workbook, what elements of the workbook import, and limitations to the import process. Power BI Desktop's auto-creation of reports and visualizations pulled from Excel workbooks is explained, demonstrating how users can enhance these reports using existing features or new ones released in the monthly updates.

The video also provides the steps needed to import Excel files from shared OneDrive for work or school folders or from Microsoft 365 group folders. The correct formatting of the OneDrive URL, while importing using the Web data source in Power BI Desktop, is emphasized. This process is clearly shown in the screenshots provided in the video.

• Users learn to select a workbook for import and are made aware of the fact that larger workbooks take longer for Power BI Desktop to analyze and import.
• The video sheds light on the conversion of the workbook into a Power BI Desktop file (.pbix), following which users can modify, save, and share it independently of the source workbook.
• The import finishing process, which shows a summary page describing the items converted and those that couldn’t be imported, is also explained.
• Finally, viewers are shown an example of an imported sales report.

To learn more about the detailed process of importing entire file content in Excel or Power BI, users can follow this video, practice the outlined steps and explore more such tutorials. They can also enroll in training courses on platforms such as Microsoft Learn to expand their knowledge of Excel and Power BI.

More links on about How to Import Entire File Content in Excel or Power BI

Get data from Excel workbook files - Power BI

Aug 28, 2023 — Import Excel data into Power BI. To import Excel data into Power BI, in My workspace, select New > Dataset > Excel, and then find the file.

Get data from files for Power BI

Jul 24, 2023 — In Power BI, you can connect to or import data and reports from these types of files: Microsoft Excel .xlsx and .xlsm files; Power BI ...

Import data from data sources (Power Query)

Select Data > Get Data > From File > From Excel Workbook. In the Excel Browse dialog box, browse for or type a path to the file that you want to query. Select ...

Excel to Power BI Tutorial: How To Address Common Issues

Jun 23, 2021 — In Power BI Desktop, click Get data and select Excel in the list of common data sources, or just click the icon that has the “x” in a green box.

Import an Excel file into PowerBI -

Mar 29, 2016 — Click the Excel then click the Connect button in the lower right hand corner. Excel Data Source Select Excel as Data Source. After clicking ...

Keywords

Microsoft Excel Power Query tutorials, Importing Excel workbooks into Power BI, Managing Power Pivot models in Excel, Excel file import in Power BI Desktop, Utilizing Excel workbook in Power BI reports.