cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User IV
Super User IV

Re: Read Excel - only selected sheet and NOT named ranges

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User II
Super User II

Re: Read Excel - only selected sheet and NOT named ranges

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

Highlighted
Frequent Visitor

Re: Read Excel - only selected sheet and NOT named ranges

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.

Highlighted
Super User II
Super User II

Re: Read Excel - only selected sheet and NOT named ranges

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors