none
Importing Data from a File - Undocumented Arguments? Also a comedy of SAP errors. RRS feed

  • Question

  • Lets say you are importing data from an Excel Workbook:

    Source = Excel.Workbook(File.Contents("c:\somedirectory\somefile.xls"))
    
    Table = Source{[Name="Sheet1", Type="Sheet"]}[Data]

    I have been unable to find any information on the arguments used in the 'Table =" line. So far all I have figured out is that, for example, in the line above, I'll be importing the "Sheet" named "Sheet1". Or I could use Name="Table1" and Type="Table" to import data from the table, Table1 in the workbook.

    Are these arguments documented anywhere? What about the [Data] part? I'm asking because I'm trying to troubleshoot a problem where PowerQuery is not importing the last two lines of a worksheet.

    Warning: Comedy of errors below!

    I'm attempting to import data from Business Objects. Unfortunately, we are using an older version that does not support the new SAP Connector. The report I created uses multiple contexts (i.e. multiple select statements), so I am unable to export as a csv file, as the report would be exported as two separate reports, stacked on top of each other. So, I have to export as an Excel file - in the older .xls format, which only supports 65,535 rows. So, my report is exported as multiple sheets in the same workbook. I'm attempting to write a PQ that will merge these sheets, but, as I stated above, PQ only pulls in data through row 65533 before moving on to the next sheet.

    Feel free to laugh at my pain! ;)


    • Edited by Strafe Friday, November 21, 2014 9:03 PM
    Friday, November 21, 2014 9:03 PM

Answers

  • In the editor, if you look at the "Source" step you'll see that it returns a table -- something we call a "navigation table". This is basically a set of properties that describe a subset of the data plus a "Data" column which references the actual data. The values in the "Table" step are simply an index into that table, and [Data] is what pulls out the specific part of the data given the values in the index.

    We haven't yet specifically documented the format of the navigation tables; they tend to be similar but not identical from source to source.

    Sunday, November 23, 2014 5:35 PM

All replies

  • In the editor, if you look at the "Source" step you'll see that it returns a table -- something we call a "navigation table". This is basically a set of properties that describe a subset of the data plus a "Data" column which references the actual data. The values in the "Table" step are simply an index into that table, and [Data] is what pulls out the specific part of the data given the values in the index.

    We haven't yet specifically documented the format of the navigation tables; they tend to be similar but not identical from source to source.

    Sunday, November 23, 2014 5:35 PM
  • You can append data together from multiple sheets by importing all the sheets (each one should become its own query), editing the query for the first sheet, and using the "Append Queries" button to append each of the other sheets to it.

    Ehren

    Tuesday, December 2, 2014 9:16 PM
    Owner
  • Thanks Curt, I saw the relationship between the statement and the navigation pane. I just wondered if there were any other arguments that might effect how the data is being interpreted.
    Wednesday, December 3, 2014 1:22 AM
  • Thanks Ehren. I have been able to append the sheets together, however, PowerQuery inexplicably ignores the last 2 rows of the first sheet, so when the sheets are merged together, i'm missing data.
    Wednesday, December 3, 2014 1:25 AM
  • That's odd. Here are a few ideas:

    • Is it possible it's accessing a named range or table on the sheet, rather than the entire sheet?
    • Is it possible a "keep first n rows" formula got inserted somehow?
    • Have you tried refreshing the preview?

    If you're comfortable sharing part of your query, you can open the Advanced Query Editor and share the formula being used to access the first sheet.

    Thanks,

    Ehren

    Wednesday, December 3, 2014 9:08 PM
    Owner