none
Can I use Power Query to Import a table from Excel sheet range which starts not from the top row? RRS feed

  • Question

  • Hi,

    Being an experienced Excel user before Power BI, I am just starting to explore the M and Power Query capabilities, and need help already (ain't easy to google this use case somehow):

    I need to import the table which sits in the Excel file with header row in the row 17 of Excel sheet, with some metadata header in the preceding rows of the columns A and B.

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

    01: Report name, Quick Report

    02: Report Date, 1/1/2014

    ...

    17: Employee Name, Manager, etc...

    18: John Doe, Matt Beaver, etc.

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

    Both (a) direct attempt to load as Excel file and (b) the indirect way through [From Folder] and formula in custom column -- both lead to the same error: "[DataFormat.Error] External table is not in the expected format."

    Specifically, I tried to use the [Power Query -> From File -> From Folder] functionality, select an Excel file and add a custom column to access the binary content: [Add Custom Column] with formula "=Excel.Workbook([Content])".

    It looks like Power Query expects a rectangular range with headers full-width followed by a contiguous table range to import anything, and refuses to load if that is not the case...

    QUESTION: Is there any way to load whatever-formatted data from Excel first, and then manipulate the overall imported range (like referring to rows starting from 17th using "Table.SelectRows" etc.) to read the actual data? Reading and using the metadata from header would be a bonus, but that comes second... The main issue is to get something from a non-regular Excel file to later work with using M formulae ...

    Thanks!

    SAM

    Tuesday, April 7, 2015 3:21 PM

Answers

  • Finally found the answer to this one in ():

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

    You Cannot Open a Password-Protected Workbook
     If the Excel workbook is protected by a password, you
      cannot open it for data access, even by supplying the correct password with
      your connection settings, unless the workbook file is already open in the
      Microsoft Excel application. If you try, you receive the following error
      message:

    Could not decrypt file.

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

    ANSWER: So, will have either weave in the work with temporary unprotected files or requires opening them before updating the data source (although this almost defeats the purpose of automation...)

    ANSWER to ORIGINAL QUESTION: password was preventing Power Query from reading the Excel file. For solution see above.

    Thanks anyway for participation and inspiration, Imke!

    • Marked as answer by BI BA Tuesday, April 7, 2015 6:45 PM
    Tuesday, April 7, 2015 6:45 PM

All replies

  • Of course it should. I'm quite surprised about the error-message. Does this come with the first step when you're importing or later?

    Are you using the UI or coding?


    Imke

    Tuesday, April 7, 2015 4:49 PM
    Moderator
  • Imke, thanks, you question prompted me to think about the context...

    I used first straight UI, then tried coding...

    BUT... I realized now that the file is password-protected, thugh. After I removed the password it reads fine.

    So the question becomes -- how do I read the file with password?

    Thanks


    • Edited by BI BA Tuesday, April 7, 2015 6:07 PM
    Tuesday, April 7, 2015 6:07 PM
  • Sorry, I have no idea - haven't come across this so far.

    Imke

    Tuesday, April 7, 2015 6:25 PM
    Moderator
  • Finally found the answer to this one in ():

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

    You Cannot Open a Password-Protected Workbook
     If the Excel workbook is protected by a password, you
      cannot open it for data access, even by supplying the correct password with
      your connection settings, unless the workbook file is already open in the
      Microsoft Excel application. If you try, you receive the following error
      message:

    Could not decrypt file.

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

    ANSWER: So, will have either weave in the work with temporary unprotected files or requires opening them before updating the data source (although this almost defeats the purpose of automation...)

    ANSWER to ORIGINAL QUESTION: password was preventing Power Query from reading the Excel file. For solution see above.

    Thanks anyway for participation and inspiration, Imke!

    • Marked as answer by BI BA Tuesday, April 7, 2015 6:45 PM
    Tuesday, April 7, 2015 6:45 PM