1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to append Productivity Tracker data into Dump file

Discussion in 'Ask an Excel Question' started by uday, Apr 15, 2018.

  1. uday

    uday Member

    Messages:
    152
    Hi,

    I want to append all three sheets data into dump file sheet 1 with power query.

    Format should be intact.

    Regards,
    Roy

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    722
    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.

    Attached Files:

    Last edited by a moderator: Apr 16, 2018
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,439
    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


    [​IMG][​IMG]





    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"
    GraH - Guido likes this.
  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    722
    Tell your friend Roger he has one extra vote, Hui. Damn'...
  5. uday

    uday Member

    Messages:
    152
    Apologies for late reply Friends. I have figured it out how to append it.

    You can stop reply on this post in further.

Share This Page