none
Power Query Versions in Excel 2016 RRS feed

  • Question

  • I bought a stand-alone (non-365) version of Excel 2016 last year and love the Power Query feature. I did some nifty things with it and management decided to buy stand-alone Office 2016 for my 7 colleagues, so they could run my Power Query Excel 2016 workbooks.

    This step to promote headers works in my version of Excel 2016: = Table.PromoteHeaders(bld_Sheet, [PromoteAllScalars=true])

    In the version of Excel 2016 that my colleagues run, the following error is produced when this step is run upon workbook Refresh All: [Expression.Error] 2 arguments were passed to a function which expects 1

    Meanwhile, no error is produced if I eliminate [PromoteAllScalars=true], leaving just = Table.PromoteHeaders(bld_Sheet) and the headers are promoted as desired.

    I suspect different versions of Power Query are in play (mine versus that of my colleagues); however, given this is not a 365 updated product versus a stand-alone version, I would have thought they had the same update channel and would therefore match. I find no way in Excel 2016 to update just the Power Query component. I have updated my version of Excel 2016, updated a colleague's version of Office 2016 and still get this behavior.

    1. If possible, how do I determine the version of Power Query in Excel 2016?

    2. How to ensure everyone is running the same version of Excel?

    3. Is there a list of Power Query functions/commands (M language) that also shows what Power Query versions support them?

    This is my first post in this Power Query forum. Thanks in advance for your help.

    Regards,

    Rick




    • Edited by ScotsSailor Monday, September 18, 2017 4:08 PM clarification
    Friday, September 15, 2017 1:02 PM

Answers

  • Hi,

    To answer your questions, then:

    >> If possible, how do I determine the version of Power Query in Excel 2016?

    Yes, it is possible. Please go to Data tab of the ribbon > click Get Data (or New Query, if you are running Excel build prior to 8067.2115) > Query Options > Diagnostics > and observe the Power Query version there.

    >> How to ensure everyone is running the same version of Excel?

    Just make sure to always download the latest Office updates and make sure you are on the same Office Update Channel.

    >> Is there a list of Power Query functions/commands (M language) that also shows what Power Query versions support them?

    I am not aware of such a list. But if you start with a blank query in Query Editor, and then just type "= #shared" in the formula bar - you will see the list of functions supported in your current version of Power Query. Click on a certain function and you will see more details about it.

    Hope it helps.

    Guy

    - Excel Team


    Guy Hunkin - Excel Team

    Tuesday, September 19, 2017 7:19 PM
  • Hi,

    I am also experiencing this problem now and then. One of the problems I found out ir that function to Promote headers has 1 parameter in older versions and 2 parameters in the newer ones. You just need to watch out in the newer versions to remove the second parameter [PromoteAllScalars=true] like in the example below:

     #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    

    It actually should read :

     #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns"),
    

    Thursday, December 6, 2018 2:07 PM

All replies

  • I also have had this issue working on 2 different versions of Excel 2016.  I remember the fix being easy but I a little surprised.
    Friday, September 15, 2017 3:30 PM
  • Hi,

    To answer your questions, then:

    >> If possible, how do I determine the version of Power Query in Excel 2016?

    Yes, it is possible. Please go to Data tab of the ribbon > click Get Data (or New Query, if you are running Excel build prior to 8067.2115) > Query Options > Diagnostics > and observe the Power Query version there.

    >> How to ensure everyone is running the same version of Excel?

    Just make sure to always download the latest Office updates and make sure you are on the same Office Update Channel.

    >> Is there a list of Power Query functions/commands (M language) that also shows what Power Query versions support them?

    I am not aware of such a list. But if you start with a blank query in Query Editor, and then just type "= #shared" in the formula bar - you will see the list of functions supported in your current version of Power Query. Click on a certain function and you will see more details about it.

    Hope it helps.

    Guy

    - Excel Team


    Guy Hunkin - Excel Team

    Tuesday, September 19, 2017 7:19 PM
  • Very helpful Guy. Thanks!

    Regards,

    Rick

    Tuesday, October 3, 2017 11:41 AM
  • Update:

    Even now, April 2018, the problem persists. It is indeed the different update paths between 365 and stand-alone product. Even though I purchased stand-alone Excel 2016, when I installed it to upgrade the version of Excel that came with my Office 365 Home account (Excel bundled with Office 365 Home does not have all features), I got the features of full blown Excel, AND also appear to be updated via the 365 path.

    I just requested 365 removal in favor of stand-alone version identical to that run by my colleagues.

    Thanks again to all.

    Rick


    • Edited by ScotsSailor Thursday, April 5, 2018 11:16 AM
    • Proposed as answer by gvvv1 Thursday, December 6, 2018 2:00 PM
    • Unproposed as answer by gvvv1 Thursday, December 6, 2018 2:00 PM
    Wednesday, April 4, 2018 5:40 PM
  • Hi Jake, do you remember that solution at all as I have the very same issue and there seems to be no help or no solutions yet. I don't know what else I can try but I couldn't find any help in Google at all.

    Regards

    <g class="gr_ gr_347 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="347" id="347">Etem</g>

     
    Monday, October 15, 2018 4:29 PM
  • Hi Guy,

    As per your advice above, how will I determine what Office Update Channel"? Even my colleagues using the very same laptops as mine have the same issue but I don't get it at all. But when I compare my Power Query version in Excel, I have 2.61 and he has 2.54 and another colleague has 2.24

    It is very strange how it can be different as we work in the same organization and have the same laptops.

    So I don't get this error mentioned above but my colleagues do.

    TRegards

    <g class="gr_ gr_855 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="855" id="855">Etem</g>

    Monday, October 15, 2018 4:32 PM
  • Hi Rick,

    Do you have any solutions to your problem at all as I do have the very same issue and couldn't find any help as of yet?

    Regards

    <g class="gr_ gr_124 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="124" id="124">Etem</g>

    Monday, October 15, 2018 4:33 PM
  • Hi EtemKaya,

    Your Office Update channel name should appear under File > Account > Product Information. Then observe the text that appears next to the About Excel button.


    Guy Hunkin - Excel Team

    Monday, October 22, 2018 4:08 PM
  • Hi,

    I am also experiencing this problem now and then. One of the problems I found out ir that function to Promote headers has 1 parameter in older versions and 2 parameters in the newer ones. You just need to watch out in the newer versions to remove the second parameter [PromoteAllScalars=true] like in the example below:

     #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    

    It actually should read :

     #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns"),
    

    Thursday, December 6, 2018 2:07 PM
  • You are so awesome-saved me a huge headache!
    Tuesday, May 7, 2019 9:04 PM