Freeze Panes issue with Power Query

Hi Mariusz 

Thank you for trying to replicate this. I think it must be something to do with the generation of the file as here is what I get when I link to it without disabling freeze panes first. The column headers are ignored, and only the first row of data is shown (the report has 20k rows in it). If I disable freeze panes and try again it's fine, headers recognised and all rows included. Unfortunately I can't share the files as it contains senstive info.  Any thoughts?

Hi, the workaround I went with was to set up a macro that I could point at a folder containing my reports which would format the data in them as an Excel Table which I could then retrieve with PowerQuery.

This is the Sub I use:

Sub Select_Reports_Folder()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String

'Macro optimisation settings
Application.Calculation = xlManual
Application.ScreenUpdating = False

'Retrieve reports folder path
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Reports Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo ResetSettings
myPath = .SelectedItems(1)
End With

'Target file extension (must include wildcard "*")
myExtension = "*.xls*"

'Target path with ending extention
myFile = Dir(myPath & "\" & myExtension)

'Loop through each Excel file in folder
Do While myFile <> ""

'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & "\" & myFile)

'Formatting changes
wb.Sheets(1).Select
If Range("A1").CurrentRegion.ListObject Is Nothing Then
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table1"
wb.Close SaveChanges:=True
Else
wb.Close SaveChanges:=False
End If

'Get next file name
myFile = Dir
Loop

'Message box when tasks are completed
MsgBox "Task Complete"

'Reset macro optimisation settings
ResetSettings:
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub