Transforming and loading multiple excel sheets with different tab names in a folder

[Anonymous]

Hi all!

I'm a newbie to Power BI (3rd week of use). I'm working with a set of data that consists of multiple excel files where each worksheet tab represents a different event's profit & loss statement. The P&L format in each worksheet is the same and is consistent; what is not the same is the different tab names. My ideal goal is to be able to put all these files in a folder path and have Power BI transform the P&L formatting into a Power BI appropriate format and append the worksheets into one combined query.

I thought I could create a series of steps modeled off these two tutorials to complete this goal, but I keep failing at duplicating the transformation across the different excel files I have:
https://blog.crossjoin.co.uk/2018/07/09/power-bi-combine-multiple-excel-worksheets/
https://www.youtube.com/watch?v=9sfCDCpWTfc

The main error seems to be that while I can individually and manually transform a particular file and all the different worksheets in that file. I can't figure out how to have Power BI automate that transformation to other excel files with their different worksheet names listed.

Is there any recommendations for how to achieve my goal? I am still learning M code, how functions and parameters work, so I am eternally grateful for any/all advice. I feel like my inability to make this happen is due to my lack of knowledge. Thanks in advance.

[Stachu]

that really depends on your setup

1) are there other sheets in the file? if not you could just fetch the first sheet, like here

let
    Source = Excel.Workbook(File.Contents("C:\test\test.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    SheetName = #"Filtered Rows"{0}[Name],
    GoToSheet = #"Filtered Rows"{[Item=SheetName,Kind="Sheet"]}[Data]
in
    GoToSheet

if there are other sheets, then maybe there is some logic in the naming, like pnl_201907 and pnl_201908 - then you could also filter for the sheets starting with 'pnl' and then fetch the first one, etc.

[Anonymous]

Hi Stachu,

I don't think that I want to just fetch from the first sheet and your second scenario doesn't apply either in terms of the naming of the sheet.

The Excel workbook would be composed of sheets with names like this:
Google
Diabetes Association
Oracle
American Heart Association
etc...

Each workbook corresponds to a month's worth of P&L data for anywhere from 6-10 companies. They aren't even the same companies from month to month, either, so that's another variable the changes. That's why I'm not sure your first suggestion works, but perhaps it still does? I do want to fetch the data from all the worksheets in the workbook, but the fact that the worksheet names correspond to different companies, and that also changes month to month, that's where my automation issue lies.

Thanks for your further trouble-shooting!