Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

InferSheetDimensions for xls files

I currently have an issue with a query returning only the first two rows and one column of data from a report. Due to the way the data is structured PQ is assuming any blank cell spacing is the end of the data. Is there a way to get excel.workbook to return the entire sheet for an xls file, where the infersheetdimensions option is not available?

 

Current state of extracting data from binary:

#"Worksheet" = Excel.Workbook(AttachmentContent, false, true){0}[Data]

 

I'm attempting to accomplish this entirely within PQ.

 

Thank you for the help!

Need Help

1 Reply

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    Instead of {0}  use a filter that identifies the sheet.  Then navigate in the sheet (remove top rows, remove columns etc) until you hit the data range.

     

    Note: .xls is a Very Bad source format and not supported in the Power BI service.