Reading a range of an Excel Sheet on a folder of files

[Chrisdenslow]

Hi

Looking for some advice.  I have a folder of Excel workbooks.  Each of these workbooks have the same format.  So I want to read them as a folder as this has worked really well in another situation.  

My issue is as follows.  I only want to read a range of cells on the sheets in each workbook as the layout on the sheets isn't straight forward.  the number of rows in the region could vary as the rows represent varying ranges of years.

I can load the folder of files and get to the transform but I can't work out how to extract the region of the sheet easily so that the Sample File Transform function will handle all my workbooks.  There are values above and below the region I need, so I was hoping to search for these phrases and record the row numbers if them.  Then select that range of rows.

is this possible, or am I better just reading everything in and doing my searches in DAX instead?

I can mock up a couple of example sheets if need be.  I can't share the real ones as they hold confidential data.

Appreciate the help

Chris

[Chrisdenslow]

Apologies for delay - I have a go live with another project i am working on and i'll take a look at this again as soon as i can.

[Chrisdenslow]

Hi Marco

Here is an example file.  Just duplicate it to make a second one for the foler uploads. 

Example.xlsm

Sadly we have no control over the fromat of the sheets - they come from a third party in that shape.  I was trying to avoid having to do any preprocessing on them before i drop them in the folder that PowerBi will be watching.

Thanks

Chris

[menglaro78]

Hi Chris,

I have included the solution in the zip file that you can download from link we transfer here

https://we.tl/t-GnUIVpngy1

I duplicated the excel file and simulated putting the first table together.

It is difficult for me to explain every single step to you, please study it as an example and I hope it will be useful for you.

This step of the function determines which range we are downloading

#"Selected Group" = #"Grouped Rows"[Rows]{3},

In the previous code you can see how it automatically calculates the ranges found in the sheet, you can also see it by analyzing the M code of the Query "Table Example"

Cheers

Marco

[Chrisdenslow]

Hi

I have tried opening the file you sent me in the latest version of PBI desktop.  Unfortunately it will not open because its sensitivity label can not be verified.  Would it be possible for you to export the file again without the sensitivity label so i can view your solution to the issue?

Thanks

Chris

[menglaro78]

Sure, sorry for this.

Remember me file name, please !

Marco

[Chrisdenslow]

Hi Marco

No problem at all - the file was called ReadExcelInFolderExample.pbix

Cheers

Chris

[Chrisdenslow]

Thanks very much - I'll take alook as soon as i can

Appreciate your time and help

[menglaro78]

Hi Chris,

if you can share a couple of sample files it is certainly easier to help you. Is the correct way to use the M language to solve your problem?

Marco

[Chrisdenslow]

Hi Marco

Thank you for getting back to me so quickly.  It'll take me a short while to put together an example of the sheet that does not hold any confidential data.   I was trying to use M, yes, but i am grateful of any advice that helps, there may be an easier way to do this.   I'll gte the sheet onto this thread as soon as i can.

Cheers

Chris

[Chrisdenslow]

Hi Marco,

I can't see an option here to attache a file easily so I have pasted in a screen shot.  Each sheet in the many workbooks i have is a series of tables at various locations on each page.  Theyt are not coloured in the real files, but I have done that for clarity here.

So what I am trying to do is read each of the workbooks using a folder transform and extract each of the coloured areas into Power BI.  As I said in my explanation above, I can't find the syntax in M that allows me to import a cell range in to my data model.

if there is a way to upload a file onto this post can you tell me how it is doen and i'll upload the file . I can only see URL options to embded a link to dropbox or something like that.

Thank you

Chris

[menglaro78]

Hello,
to attach a file use an external service such as OneDrive, GoogleDrive, DropBox, WeTransfer.

In your example, can't the different colored areas each fit on a different sheet?
Anyway if you send 2 example Excel, I send you some tips on how to handle them.

Marco