Forum Discussion
Freeze Panes issue with Power Query
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
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 DannyMcMate
Try to mark this table as Table in Excel
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
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
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.
LinkedInHi 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"