Get data from Folder -iterate through sheet names

[Anonymous]

Hi all,

Have a folder with 15 excel files for 15 customers out of which need to prepare a weekly report with combined data.

Each customer files contains week no# sheets with that customer's orders throughtout the year etc: "week 41",  'week 42" and so on. How could I load from folder (I know how to do this one) but then iterate through the sheet names of each file,  take the data out of the one with the bigest week no# and append everything for further transformations ?

Thank you,

Mihnea

[v-alq-msft]

Hi, @Anonymous 

The combine files transform behaves as follows:

• The combine files transform analyzes each input file to determine the correct file format to use, such as text, Excel workbook, or JSON file.

• The transform allows you to select a specific object from the first file, such as an Excel workbook, to extract.

The combine files transform then automatically takes these actions:

• Creates an example query that performs all the required extraction steps in a single file.

• Creates a function query that parameterizes the file/binary input to the exemplar query. The exemplar query and the function query are linked, so that changes to the exemplar query are reflected in the function query.

• Applies the function query to the original query with input binaries, such as the Folder query. It applies the function query for binary inputs on each row, then expands the resulting data extraction as top-level columns.

You can easily combine all files within a given folder if they have the same file type and structure (such as the same columns and sheet names).

For further information, please refer to the following document.

Combine files (binaries) in Power BI Desktop 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

The sheets in each of my files are as in the picture, and each week, a new sheeet with that sheet no# is added; I would like during the load form folder to identify the bigest week no# and then combine and load those sheets together.  Is it possible? 

Thank you

Mihnea 

[Fowmy]

@Anonymous 

Please check out this short video, it does what you are expecting.

https://www.youtube.com/watch?v=GvKYUgka_4Y

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thanks, interesting one but in my case the sheet names inside each file change on weekly basis; would be looking for a dynamic way to get the data from the latest one in terms of week no# sheet name..

Thank you