Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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?
Please refer to this blog:
https://www.sqlchick.com/entries/2018/5/6/querying-data-in-azure-data-lake-store-with-power-bi
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
107 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
94 | |
91 |