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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MichaelJ64
Frequent Visitor

Read Excel - only selected sheet and NOT named ranges

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.

 

4 REPLIES 4
Jimmy801
Community Champion
Community Champion

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

image.png

 

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

Greg_Deckler
Super User
Super User

@MichaelJ64 - Probably not but @ImkeF and @edhans might know better.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors