Need to append Productivity Tracker data into Dump file
- Thread starter uday
- Start date
GraH - Guido
Well-Known Member
Hi Roy, I got an error when trying to read .xlsb with PQ. I was surprised, but it seems not to work with that extension like it does for a standard .xlsx-format.
After some trial and error finally got it to read the data from the file, but a lot of extra messy code was added, I do not understand. Started to read from folder and it invoked a function automatically.
Got to figure it out first.
With .xlsx format, the PQ could be like attached. If you want the same cell format as in the original file, just define it once in the final table. That definition should be kept even after a refresh with new data in the source file.
EDIT: in the source replacing
= Excel.Workbook(File.Contents("G:\Uploads\Productivity Tracker_1.xlsx"), null, true) by
= Excel.Workbook(File.Contents("G:\Uploads\Productivity Tracker_1.xlsb"), null, true)
simply worked.
So in the source step, just replace the pat and the correct filename. PQ should work.
After some trial and error finally got it to read the data from the file, but a lot of extra messy code was added, I do not understand. Started to read from folder and it invoked a function automatically.
Got to figure it out first.
With .xlsx format, the PQ could be like attached. If you want the same cell format as in the original file, just define it once in the final table. That definition should be kept even after a refresh with new data in the source file.
EDIT: in the source replacing
= Excel.Workbook(File.Contents("G:\Uploads\Productivity Tracker_1.xlsx"), null, true) by
= Excel.Workbook(File.Contents("G:\Uploads\Productivity Tracker_1.xlsb"), null, true)
simply worked.
So in the source step, just replace the pat and the correct filename. PQ should work.
Last edited by a moderator:
I received this email from Roger Govier a fellow MVP just this last week
I have copied it here in full as it is very relevent:
"As many of you know, I am a great advocate of using .xlsb file format for saving files.
Many of my client’s files are very large and saving in .xlsb format can achieve file size reductions down to 1/3 of their equivalent.xlsm files.
However, as I try to get more users making use of PQ / Get Data, the loading of data is different for .xlsb compared with the other file formats.
With .xlsb, you only see a list of Sheets in the file, not the list of Tables as with .xlsx or .xlsm
.xlsx or .xlsm .xlsb
It is still possible to extract and combine the data, but if there is more than one Table on a sheet, or the Table doesn’t start in cell A1 then there are lots of extra steps involved in deleting unrequired rows and columns, and it becomes more difficult to teach and end user and get them to adopt the fabulous Power Query.
I have raised a Uservoice request to get this changed, even if it means a new connector just for .xlsb files.
I would be grateful for any support from you all.
https://excel.uservoice.com/forums/...tables-in-power-query-when-opening-an-xlsb-fi
Regards
Roger Govier"
I have copied it here in full as it is very relevent:
"As many of you know, I am a great advocate of using .xlsb file format for saving files.
Many of my client’s files are very large and saving in .xlsb format can achieve file size reductions down to 1/3 of their equivalent.xlsm files.
However, as I try to get more users making use of PQ / Get Data, the loading of data is different for .xlsb compared with the other file formats.
With .xlsb, you only see a list of Sheets in the file, not the list of Tables as with .xlsx or .xlsm
.xlsx or .xlsm .xlsb
It is still possible to extract and combine the data, but if there is more than one Table on a sheet, or the Table doesn’t start in cell A1 then there are lots of extra steps involved in deleting unrequired rows and columns, and it becomes more difficult to teach and end user and get them to adopt the fabulous Power Query.
I have raised a Uservoice request to get this changed, even if it means a new connector just for .xlsb files.
I would be grateful for any support from you all.
https://excel.uservoice.com/forums/...tables-in-power-query-when-opening-an-xlsb-fi
Regards
Roger Govier"