Freeze Panes issue with Power Query

[Syndicate_Admin]

Hi all,

I have an excel spreadsheet report that has by default when generated freeze panes enabled to ensure the column headers are always visible. This said, having freeze panes enabled in the file seems to interfere with Power Query being able to correctly select the header row when I try to import data from this file, Power Query seems to only see the row directly below the row that freeze panes has been enabled on and truncates the rest of the report.

I am aware I can open the report and disable freeze panes, or turn the range into a table to correct this, but ideally I want to be able to just save the report into a folder and not have to open and edit it at all.

Thanks in advance!

D

[Syndicate_Admin]

Hi @DannyMcMate 

Just tested it and seems fine on my end.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Syndicate_Admin]

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?

[Syndicate_Admin]

Hi @DannyMcMate 

Try to mark this table as Table  in Excel 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Syndicate_Admin]

Hi @Mariusz 

I was hoping to find a method of correcting this in Power Query without having to open the files as there are many similar files that need to be saved down each month for this dashboard. I think this probably isn't possible though, the program generating of the files must be doing something weird and so the only option is to open and edit each file either by converting to a table as you suggest or disabling Freeze Panes before saving 😞

Many thanks,

D

[Syndicate_Admin]

Hi @DannyMcMate 

Can you sen the script from the advanced editor?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Syndicate_Admin]

Hi @Mariusz 

Here is the script with file name removed from File.Contents().

let
    Source = Excel.Workbook(File.Contents(), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type date}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type any}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type date}, {"Column18", type date}, {"Column19", type any}, {"Column20", type any}, {"Column21", type text}})
in
    #"Changed Type"

[Syndicate_Admin]

Hi @DannyMcMate 

Sorry, there is nothing I can do with the code to fix the issue.

You can try naming the rage and see if it helps

Mariusz

[Syndicate_Admin · Accepted solution]

It would be rather easy to write a VBA macro (or even a script in PowerShell or Python) that would go through all Excel files in a folder, silently open them without even showing the file on the screen, change the setting on every sheet and then save them. No manual work required and very quick. Excel is an automation OLE server, so you can automate it from almost anything... All you need is to import the Excel object library which can be found on every computer with Excel.

Best
D

[Syndicate_Admin]

hi I see same issue as of 7/2023. what is plan to fix, what is workaround?