Skip to content

Import Multiple Excel Workbooks Using PowerQuery M In Gen1 Dataflows

Why we use dataflows

In our setup, we use Power BI Dataflows Gen1 specifically to load and prepare data from Excel files.

This means:

  • All dataflows are Gen1 dataflows
  • All sources are Excel files
  • We clean, reshape, and standardize the Excel data in the dataflow
  • The prepared data is stored in the Power BI Service
  • Datasets only consume already-prepared data

This allows us to handle Excel-related changes (file structure, column names, fixes) once in the dataflow instead of duplicating logic in multiple files.

Steps to take

In summary, you need to:

  1. define a main query -> filter for the Excel sheets you want

  2. expand the Excel sheets

  3. Power BI will create all the necessary elements for you.

  4. clean up and do consistent renaming

  5. do NOT touch the function. It is wired to your function query.

  6. apply your custom logic in the function query. Test it out with different parameters (= different Excel workbooks) if necessary. Make sure that types are consistent, column headers are correct, etc.

  7. in your main query, replace the expansion. Instead of the column from the sample file, use the actual headers from you function query

import-multiple-excel-workbooks.png

Elements

The elements at the end of the process will be:

  1. a main query, e.g. Geen LVZ workbooks main query

  2. the main query will get all the files to process

  3. for each file, it will call a power query function (fx), e.g. 'Geen LVZ worksheet transformation function'. Do not touch this.

  4. under the hood, the function is linked to a function query, e.g. 'Geen LVZ worksheet transformation function query'

  5. the function query contains all the transformation logic of a single file

  6. you can change the transformation logic by adding steps to the function query. You can also debug a single file using this query.

  7. to select a specific file, e.g. to debug a specific problem, set the parameter accordingly

  8. make sure you remove the reference to the parameter (sample file) added by Power BI, and re-add the expansion steps again manually

import-multiple-excel-workbooks-assets.png