none
Issue with getting a named range table content into Power Query (Get & Transform) in Excel 2016 RRS feed

  • Question

  • Hello,

    After the latest Excel 2016 update (version 16.0.6741.2017) Get & Transform (Power Query) is not importing properly a named range from the spreadsheet. For example, if I create a named range "MyRange" and then use Excel.CurrentWorkbook(){[Name="MyRange"]}[Content] in Get&Transform, this will produce a table with the same number of columns as in my named range but content is missing (the table content is all nulls everywhere). Is this a known issue? I was able to reproduce the issue on two different systems. Any workaround?

    Thanks for your help.

    Best regards,

    LG

    Monday, March 21, 2016 6:05 PM

Answers

  • Thank you for raising this issue.

    We found the bug and inserting a fix.

    This happens in cases where offset of the column and row offset of the first cell of the range is not equal. Meaning if your range starts at A1 or B2 it will be OK, in A3 range will not be imported correctly.

    Thank you,

    Penina [Microsoft]

    Wednesday, March 30, 2016 12:38 AM

All replies

  • Having the same issue where named ranges are not picking up the correct range.
    Wednesday, March 23, 2016 9:24 AM
  • Hi, thank you so much for reporting this. The Excel team is investigating a fix. I'll report back in when I have more information.

    Freya

    Microsoft Office Newsroom

    Monday, March 28, 2016 4:19 PM
  • And it works just fine in Excel 2013 Power Query.  Just appears to be Excel 2016 latest update only.

    ChrisM26

    Tuesday, March 29, 2016 1:47 PM
  • Chris, are you also on Excel version 16.0.6741.2017?

    Ehren

    Tuesday, March 29, 2016 7:05 PM
    Owner
  • Currently on version 16.0.7641.2021 with the same issue.

    ChrisM26

    Tuesday, March 29, 2016 7:13 PM
  • Thank you for raising this issue.

    We found the bug and inserting a fix.

    This happens in cases where offset of the column and row offset of the first cell of the range is not equal. Meaning if your range starts at A1 or B2 it will be OK, in A3 range will not be imported correctly.

    Thank you,

    Penina [Microsoft]

    Wednesday, March 30, 2016 12:38 AM
  • Thanks!  Any ETA when the fix might be implemented?

    ChrisM26

    Friday, April 8, 2016 5:59 PM
  • Hi, we have released a fix for this. Please update your Excel and let us know if the problem persists.

    Thanks!

    Freya

    Office Newsroom

    Thursday, April 21, 2016 4:38 PM
  • Hi.  I'm still having the same issue.

    My Office version is:

    16.0.6769.2017

    And my Excel version is showing 16.0.6729.1014.


    ChrisM26

    Wednesday, April 27, 2016 2:58 PM
  • Chris, can you try manually pulling the latest update via File > Account > Update Options > Update Now?

    We just started rolling out a new build (16.0.6769.2040) that fixes the bug that Penina mentions earlier in this thread. Specifically, update 16.0.6769.2040:

    • Fixes an issue where a named range is incorrectly imported when using Data > Get & Transform > From Table.
    • Fixes an issue where macros run noticeably slower on computers where logging is enabled for the Telemetry Dashboard.

    Here are release notes, if you need 'em. 

    Sorry for the earlier confusion.

    Anneliese




    Friday, April 29, 2016 12:43 AM
  • The update came available today and now on the new build and it did fix the issue!

    Many thanks!

    Appreciate your following-up.  You guys are awesome.


    ChrisM26

    Friday, April 29, 2016 1:35 PM