Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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.

2 REPLIES 2
Stachu
Community Champion
Community Champion

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.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors