Select Excel Worksheet from Datalake Binary Excel File

[ncroston]

Hello,

I am a Power Bi newbie taking over tasks for someone that left where I work. I have been able to follow along and get around in Power Bi somewhat, but there is one important thing that I cannot determine.

We store Excel files in Azure Data Lake. The files are loaded into Power Bi as binary files. Each Excel workbook has 14 worksheets.

Our query always selects the same worksheet out of 14 but I cannot figure out why the one is selected rather than others. If I want to change the selected worksheet, I do not see where to specify. Our code is as follows.

let
Source = DataLake.Contents("<source_location>", [PageSize=null]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Name", "Transform File"}),
#"Expanded Transform File" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Transform File.Column1", "Transform File.Column2", "Transform File.Column3", "Transform File.Column4", "Transform File.Column5", "Transform File.Column6", "Transform File.Column7"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Transform File", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Date] <> "Date")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Seasonal Forecast", "Seasonal Monthly Forecast"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Total Non-Aeronautical Revenue"})
in
#"Removed Columns"

Any guidance is greatly appreciated.

[ncroston]

Thank you @V-lianl-msft. This is a good read, but it doesn't quite answer the issue that I am experiencing.

Each of my spreadsheets have multiple worksheets. I cannot determine how the connection to each spreadsheet determines which worksheet is active.

Would you happen to know how that is determined?

[V-lianl-msft]

Please refer to this blog:

https://www.sqlchick.com/entries/2018/5/6/querying-data-in-azure-data-lake-store-with-power-bi