Importing Multiple Selected Excel Sheets from multiple workbooks at one time

[lcoetzee]

Hi,

Is there an easy way to import multiple sheets from an excel workbook based on what the sheet name contains.

For example, I have 3 excel workbooks with similar data in it. All the workbooks contain financial and production data from differrent sites.

I would like to import first all the data from the sheets where the sheet name contains financial and do the same for the production data.

BUT, here is the trick. My columns of the data are as follows:

  -  Open Column

  -  Item Measure

  -  Unit of Measure

  -  Value 2007

  -  Value 2008

  -  Value 2009

However, the year colums per sheet does not match....some start in 2007, others in 2015, other in 2030 and they also do not end on the same year, so the column names for the years in each sheet will be different.

Is there a way to have a standard table and just append these sheets directly from excel into the table?

Thanks

[lcoetzee]

Thank you. Your comments gave me the answer..... It seems that I will have to do each one on its own due to the years that are in the columns?  There are 62 sheets in total, so I was hoping that there is a way to do it much faster....without havign to unpivot each. I will try and get sample data and send.

[dufoq3]

Hi @lcoetzee, it is possible but provide some dummy excel files please.

[lbendlin]

As part of your ingestion you need to unpivot the year columns.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...