A Power BI developer needs to load data from an Excel workbook that contains multiple sheets. Some sheets (e.g., 'Sales_Q1', 'Sales_Q2') contain transactional data that needs to be combined, while other sheets (e.g., 'Lookup_Products', 'Settings') contain lookup tables or configuration data that should be loaded separately. When using the Excel workbook connector, how can the developer efficiently select and manage these different types of sheets?
- AIn the Navigator, select multiple items (tables/sheets) and load them as separate queries.
- BSelect all sheets, then filter rows in Power Query Editor to separate data.
- CLoad the entire workbook as a single table, then split it by sheet name.
- DUse the 'Get Data' feature multiple times, selecting one sheet at a time.
Show answer & explanationAnswer & explanation
Correct answer: A. In the Navigator, select multiple items (tables/sheets) and load them as separate queries.
The most efficient way is to use the Navigator within the Excel workbook connector to select all required sheets/tables simultaneously. Power Query will then create separate queries for each selected item, allowing individual transformation and combination as needed.
Why the other options are wrong
- B. Selecting all sheets and then filtering rows would be inefficient and complex, as it would require identifying the source sheet of each row and then splitting the data, which is not how Power Query typically handles multiple sheets.
- C. Power Query does not load an entire workbook as a single table and then split by sheet name; it presents individual sheets/tables for selection in the Navigator.
- D. While technically possible, using 'Get Data' multiple times is less efficient and more time-consuming than multi-selecting in the Navigator for the same source file.
Excel Workbook Connector (Multiple Items)
The Power Query Excel Workbook connector allows you to select and import multiple sheets, named ranges, or tables from a single Excel file. The Navigator pane enables multi-selection to create separate queries for each chosen item, facilitating flexible data preparation.
- Connects to .xlsx, .xlsm, .xlsb files.
- Navigator shows sheets, named ranges, and tables.
- Allows multi-selection to create multiple queries efficiently.
Memory trick: The Excel Navigator is like a table of contents; pick all the chapters you need at once.