Efficiently Combining multiple excel workbooks and worksheets in Excel sharepoint


#1

Hi Sam,

I have multiple excel workbooks in sharepoint with multiple worksheets that I want to extract data from and consolidate into BI.

The columns in each worksheet are not consistent and I don’t need every worksheet in each workbook unfortunately as I don’t control these inputs

My question is how can I do this efficiently so it only looks at the same workbook once instead of having a lot of transform files?
Maybe reference an original transform query so that it looks at multiple worksheets?

Thanks

Richard


#2

Have had a good think about this one.

It doesn’t seem like there’s much consistency around what you are querying. The columns aren’t consistent and you don’t require every worksheet (which I presume can change up also)

I think it really will be quite different to come up with a solid solution here unless you have some consistency around the data inputs.

That’s really how the query editor can work best. It likes to see and do the same process over and over again without fault. As soon as new variable appear in either the data or data location this can become a bit of a problem.

Let me know if I’m missing something here, but my recommendation at the moment is to get the raw data into a better format before going to grab it with the query editor.

Personally I would work on the data usually than attempt to place bandaids over it in the query editor. If you do this then you run a big risk of a consumer looking to update the data and then getting an error and not knowing what the issue it. I’ve seem this happen too many times.

Hopefully this gives you some ideas


#3

Thanks for the response Sam. Unfortunately I don’t have any control on the structure of the raw data. SharePoint is not the best data source either as its painfully slow

I would have thought that the query editor should have a function to only read the same file once but it seems to have to grab a transform file for each sheet on the same workbook?

Thanks

Richard


#4

When you say a ‘function to only read the same file’, I’m not too sure what this means exactly.

Are you able to share some images of the scenario? That allows assist in fully understanding what you are dealing with.

The reality is though, that even though Power BI is immensely powerful, sometimes raw data needs to be curated a little bit to make sure the connection and querying of the data is easy and effective.

Is the data structure is constantly changing and the users have no standard way to input things, there’s invariably always going to be some problems which are difficult to sort out even in the query editor.


#5

Also not sure if you have checkout out this section at Enterprise DNA Online, but this covers all the key functionality in the query editor.

This might give you some good ideas to work with.