Import multiple Excel Files

[Anonymous]

Hi,

I have a question on best practice for pullign data from Excel. Each project leader has an Excel-file with 12 sheets, 1 for each month. They have around 60 project leaders that are reporting into their own Excel-file which means there are 60 files with 720 sheets.

Each sheet has information on different projects that they can work where they fill in the date they worked on for a particular project. Each day is one column and they like the output so i'm unpivoting in Power Query and it works fine. My question on this is if it's feasible solution to do, it ends up to 3 million+ rows that's updated daily. They will have Power BI Pro licenses.

Does it make sense to do it this way since the base Excel structure will not be changed much? I'm removing rows and columns in Power Query so that would need to be improved as well, would that be with parameters? Such as removing a particular column or rows, how do I make it with M code the best way?

Hope the question makes sense.

Kind regards,

Jari

[Daryl-Lynch-Bzy · Accepted solution]

@Anonymous - I would like to start by suggesting you create a Power App to manage this instead of using Excel.

From a Power BI point of view, here is what is running through by mind:

1. I would aim to reduce the number of worksheets processed when I hit refresh.  All 720 does not make sense.  Separate the Power Query refresh using Dataflow to read Jan, Feb, Mar etc separately.  Now later in the year you will only need to update 1 to 2 months max.
2. Use a function to read only the dates (columns) and cost centers (e.g. rows) with data from the individual worksheet, then combined with the other worksheets.   In other words, don't import all Worksheets into single table, then unpivot and filter.  Read the worksheet for Jan, select the columns before 1/1/2022, then use Unpivot Other Columns to get date. Then filter to remove blank.  Power Query will cycle through the 60 workbooks performing this for each.  Then expand the results into a table.  Check out these videos:
   • Part One - https://www.youtube.com/watch?v=UY3hIV-THcg
   • Part Two - https://www.youtube.com/watch?v=hJF2dPfIeaM