Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Community,
I wrote a Power BI query to process data contained in small Excel workbooks. The workbooks generally have three worksheets in them -- the actual request, and two examples one showing a "good" request and one a "bad" request. These workbooks are e-mailed.
Unfortunately, some of the workbooks have acquired a maximally-sized $Print_Area on all three sheets. The program is slow to run as it 'reads' in these maximally sized tables even though they are not used.
Is there a way to tell Power BI not to bother reading in the named ranges and unused sheets?
Here is my code:
let
Source = Exchange.Contents("Michael.Johnson@mywork"),
Mail1 = Source{[Name="Mail"]}[Data],
FilteredRows = Table.SelectRows(Mail1,
each (Text.Contains([Folder Path], "Test") and not Text.Contains([Folder Path], "Sent") ) ),
ExcelAttachments = Table.SelectRows (FilteredRows, each
(Text.Contains([AttachType],"excel") or Text.Contains([AttachType],"spreadsheet") or
Text.Contains([AttachType],".xlsx") ) ),
ExcelWorkbook = Table.AddColumn(ExcelAttachments, "RequestWorkBook", each
Excel.Workbook([AttachContent], null, true) ),
ExpRequestWorkBook = Table.ExpandTableColumn(ExcelWorkbook, "RequestWorkBook", {"Name", "Data"}, {"Name", "Data"}),
SelectedSheets = Table.SelectRows(ExpRequestWorkBook", each
( [Name] = "Request Details" or [Name] = "Request details"
or [Name] = "Sheet1" ) ),
ExtractXLS = Table.AddColumn(SelectedSheets, "NewRowQueryElements", each
ExtractQueryToAdviceTable([Data] ) ),
where ExtractQueryToAdviceTable is a function I wrote to parse the table holding the request to extract certain parameters. It expects to be sent a table. [Data] is a table created from the request worksheet.
Thanks,
Michael J.
Hello @MichaelJ64
The function Excel.Workbook gets back a table with all objects. The column "Kind" tells you what type is it. So just implement a filtering step after Excel-Workbook
Here the code to filter for "Sheet"
let
Source = Excel.Workbook(File.Contents("YourExcelFile"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet"))
in
#"Filtered Rows"
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Jimmy801
Thank you for your prompt reply and your suggestion. Your suggestion has helped me better understand how the code works, and importantly helped me find a file oddity - an Excel attached sheet that had type "null"!
Your suggestion did seem to make the code run faster within the Query editor, but unfortunately when I launched "Apply query changes", the dialogue box showed that it still went off to read in the unused sheets and unused named ranges.
So, I hunted down which line caused the sheets and ranges to be read in. On my system it's the code that invokes Excel.Workbook. Bugger. Reads in everything so it can be available and THEN I can filter it. I was hoping for PowerBI in "lazy" mode rather than "prepared" mode. I can live with this because the intention is to run the program automatically in the early hours of the day. I was hoping to make it quicker, especially while I refine it. I'm still exploring the ways people can alter a pro-forma 🙂
Thanks,
Michael J.
Hello @MichaelJ64
hmmm... i don't know which table your query is referring, but why it should read from unused sheets when your are them filtering out? Or you applied the filter wrongly or you are bypassing the filter step. Maybe the query is also slowly because of multiple reading of the file itself or you didn't disable the power query background refresh
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
@MichaelJ64 - Probably not but @ImkeF and @edhans might know better.