Only first row displaying

[Iamzuup]

I'm compiling reports from a shared folder. The problem I'm having is only the first row is returning per sheet.

I've read related posts and one solution that worked was going into the source sheets, saving them, closing and refreshing the query. Ideally I don't want to have to do this step every time.

let
Source = Excel.Workbook(File.Contents("Path"), null, true),
Status_Sheet = Source{[Item="Status",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Status_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"E-mail Subject", type text}, {"Contractor", type text}, {"Transaction Start", type any}, {"Transaction End", type any}})
in
#"Changed Type"

 I'm expecting the following, however only the first row is returning

E-mail Subject	Contractor	Transaction Start	Transaction End
Email 123	Contractor Name 123
Email 123	Contractor Name 123

 Going to the source step, when looking into the table, only one row shows. So not entirely sure what the issue is.

[OwenAuger · Accepted solution]

Hi @Iamzuup 

Just a hunch, but it's possible that some worksheets within the Excel files have incorrect "dimensions" specified in their metadata. This can happen when the files are generated by a 3rd-party tool (for example).

A possible fix is to change the Source step to:

Source = Excel.Workbook( File.Contents("Path"), [DelayTypes = true, InferSheetDimensions = true ] ),

The InferSheetDimensions field in the record specifies that sheet dimensions should be based on the data present in the sheet, not the metadata.

The DelayTypes field is not related to your specific issue, but specifies that columns are left untyped (since the types are changed in a later step).

Does this help? If not, it must be something else.

Read more on this here:

https://learn.microsoft.com/en-us/power-query/connectors/excel#missing-or-incomplete-excel-data

https://blog.crossjoin.co.uk/2021/01/17/new-option-to-solve-problems-with-power-query-not-loading-al...

[OwenAuger · Accepted solution]

Hi @Iamzuup 

Just a hunch, but it's possible that some worksheets within the Excel files have incorrect "dimensions" specified in their metadata. This can happen when the files are generated by a 3rd-party tool (for example).

A possible fix is to change the Source step to:

Source = Excel.Workbook( File.Contents("Path"), [DelayTypes = true, InferSheetDimensions = true ] ),

The InferSheetDimensions field in the record specifies that sheet dimensions should be based on the data present in the sheet, not the metadata.

The DelayTypes field is not related to your specific issue, but specifies that columns are left untyped (since the types are changed in a later step).

Does this help? If not, it must be something else.

Read more on this here:

https://learn.microsoft.com/en-us/power-query/connectors/excel#missing-or-incomplete-excel-data

https://blog.crossjoin.co.uk/2021/01/17/new-option-to-solve-problems-with-power-query-not-loading-al...

[Iamzuup · Accepted solution]

Hey, worked like a charm, I appreciate it!

Is there a way to implement this into a folder.files activity?

[OwenAuger]

Glad to hear it 🙂

Yes, as long as you make the above change wherever Excel.Workbook is called, it should apply to every file within the folder.

This is likely within a "Transform Sample File" query if you have used the Power Query interface to combine the files.

Could you give more detail on the overall setup of your queries?

[Iamzuup]

Again, thanks so much for your time and help, updating this at the transform sample file fixed the issue.