Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 state...
Need Help
Tutorial Requests
Stachu's avatar
Stachu
Community Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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!