none
Power Query Load to Data Model -- Constantly Stuck/Hanging RRS feed

  • Question

  • Hello,

    I am currently running into a troubling situation across with Power Query and Power Pivot across multiple computers.

    Both computers are Windows 10 Pro 1903

    Both versions of Office are Microsoft Office Professional Plus 2016 and up to date.

    I cannot under any circumstances load a Power Query based query/table into the Data Model. Whether it's 26 rows in a totally simple by-hand table (see below) or a complex query that's had a lot of transformation and many tens/hundreds of thousands of rows, the same thing happens:

    EDIT:

    I cannot submit an image file/link given my account is not verified. I am not sure how to show what is happening given that.


    It just hangs and hangs and hangs and never loads to Data Model. The number of rows always loads instantly, but then it hangs after that.

    Things I can do to get the Data Model to work:

    1. I can create a table in Excel and load directly to Data Model (From Table option in Power Pivot Ribbon). That's fine and instantaneous.
    2. I can load the Power Query query to a table in the worksheet and then load that query indirectly to Data Model by using the From Table option like I would a regular Excel Table in the first option. 

    What I can't do:

    I can't right-click the query (or choose upon initial creation) the option to "Load to Data Model" regardless of whether it's a Connection Only or Load to Worksheet. All Excel does is hang and nothing ever shows up in the Power Pivot window after many minutes of waiting.

    I can't load bigger tables to worksheet as an intermediate step so this work-around is not stable or usable.

    Over the last 24 hours I've tried: 

    • Repairing Office (Full Repair that takes a long time and re-downloads Office essentially)
    • Uninstalling/Reinstalling .Net
    • Repairing .Net Installation with the Microsoft Tool
    • Changing my Power Query options/configuration (Background Data, etc) per other sites/forums where I've seen  similar (but not the exact same) issues. 
    • Searched for hours on forums and found nothing that troubleshoots this issue.

    This is a new problem given I was just using Power Query and Power Pivot last week with no issues. It's happening across multiple machines. It's happening with generic, tiny tables like the one in the gif above (with no other data in the workbook) and bigger, more complex queries that cascade off of each other, have transformations, etc. Same results no matter what.

    If anyone has any idea what I might do to fix this, I'd be very grateful. This is impacting my ability to do client work given it's not working on either of my computers.

    Thanks so much!


    • Edited by Wizard-121 Thursday, November 21, 2019 3:09 PM
    Thursday, November 21, 2019 3:08 PM

Answers

  • Thank you all for reporting the issue and for all the details you have provided.
    As you may have already noticed, we finally found the root cause of it and according to our testing it has been fixed.
    Please restart Excel before trying it again.
    If you aren't using one of the latest versions already, you may have to update your installation first.

    Yehezkel,

    Excel team



    Sunday, February 2, 2020 6:08 PM

All replies

  • I just went back to an older workbook from a few weeks ago that has ten queries and two scrubbed queries loaded into the data model that has never given me problems.

    I tried to add an additional query to the data model and the same behavior that I described above happens.

    In short, I have no ability whatsoever to combine Power Query and the Data Model/Power Pivot currently.

    I have no idea what to do to fix this.

    [EDIT] One more update:

    I opened my original file on a client machine remotely. Office 365 ProPlus. Zero problems. Everything works fast, effortlessly, and easily. No issues with the data model, PQ >> PP interface, hanging, etc.

    I have a feeling there's something wrong with Office 2016 Professional Plus standalone.


    • Edited by Wizard-121 Thursday, November 21, 2019 9:28 PM
    Thursday, November 21, 2019 4:23 PM
  • Hi there, I've got exactly the same issue, but I've seen it in Windows 10 and in Windows server 2012 R2 64 but always with Excel 2016 professional plus.

    I am an instructor in Excel and I was teaching a course on Power Query and Power Pivot 3 weeks ago and this issue happened to all the students that happened to have Excel 2016 professional plus with the latest update installed (October). It's the first time in 3 years teaching with the same material.

    After many tests I have come to the conclusion that PCs with Excel 2016 professional plus 32 or 64 bits with the version 1910 compilation 12130.20410 or 12130.20390 are affected with this issue.

    There is no way to load data from any query (CSV, excel, access,..) to the data model. Importing directly from Power Pivot works fine so I guess the addin is ok. And also, when loading to table, the process stops and finally finishes by cancelling the load and then refreshing. Doing the same procedure when loading to the data model just ends in an error.

    I've tried playing with the windows defender new options, with different data sources, but nothing. In my laptop with 365 pro plus it just works like charm.

    It is a very unnerving situation that I hope gets addressed soon.

    I'll post any updates I may find on this subject so it can be helpful to others.

    Cheers

    DU

    Tuesday, November 26, 2019 9:35 PM
  • Hi DU,

    Thanks so much for validating this issue. At least I know I am not crazy or doing something weird or wrong. My only solution was to use a different machine that has a different version of Excel on it. Both Excel 365 and Excel 2019 worked for my situation and loaded the data seamlessly and quickly. The Excel 2016 machines had no ability to do what I needed them to as of my writing (I haven't tried again since then).

    I am surprised more people haven't reported a similar issue. That's part of what made me think it was something local to my machine/version/add-ins. When the second machine showed the same issue, I figured it was the software, but I haven't seen anyone else complaining about it and I'm quite sure that there are many many thousands of people using Power Query/Power Pivot with Excel 2016 Professional Plus.

    Hopefully it gets resolved shortly.


    • Edited by Wizard-121 Thursday, November 28, 2019 2:21 PM
    Thursday, November 28, 2019 2:20 PM
  • hello

    posting the query would be appreciated.. .:)

    do you use some external tables to do filering in the query?

    I had a similar problem and it turned out that it was some kind of a bug, but there was a work around.

    Please check my post and check if the work around suggested there is working for you

    https://social.technet.microsoft.com/Forums/en-US/4b5eb556-2d27-4c98-983e-d1cef591d8cb/problem-loading-data-directly-to-data-model?forum=powerquery

    good luck

    Jimmy


    Query it

    Thursday, November 28, 2019 3:32 PM
  • Any query would fail I'm afraid. Check this video so you can see what kind query is not loading. The simplest one won't load into the data model.

    https://www.screencast.com/t/0gr2QTq36

    I think it has something to do with october 8th update and just for Excel 2016 users.

    DU

    Thursday, November 28, 2019 9:12 PM
  • oooh... this seems to be a bug indeed.

    Because I'm curious... try to add a Table.Buffer after the IN-statement...

    something like

    in
        Table.Buffer(ChangedType)

    Jimmy


    Query it


    • Edited by jimmy80211 Friday, November 29, 2019 10:41 AM
    Friday, November 29, 2019 10:39 AM
  • In addition to DU - as of today - 2 other users running Excel 2016 face the same issue and nobody was able to assist (MS Community thread). After having seen this thread I suggested them to report here as well suspecting something specific to this version
    Friday, November 29, 2019 1:44 PM
  • hello

    posting the query would be appreciated.. .:)

    do you use some external tables to do filering in the query?

    I had a similar problem and it turned out that it was some kind of a bug, but there was a work around.

    Please check my post and check if the work around suggested there is working for you

    https://social.technet.microsoft.com/Forums/en-US/4b5eb556-2d27-4c98-983e-d1cef591d8cb/problem-loading-data-directly-to-data-model?forum=powerquery

    good luck

    Jimmy


    Query it

    Hi Jimmy,

    As DU said, this fails for all queries. I have tried multiple workbooks that have never had any issues -- some freshly made, some old and elaborate, some that were super basic.

    My original post tried to upload a gif that showed this issue for the most basic of query loads (an Excel Table of 1/2/3; A/B/C) but I wasn't allowed to embed any photos or links given I was new here.

    Here's what I am talking about. This is by far the most basic Power Query to Data Model/Power Pivot table one can add and it still hangs.

    Friday, November 29, 2019 5:11 PM
  • I am having the exact same problem. Any CSV file folder will fail. I have the exact same version of Excel  - Professional Plus 2016 with Version 1910 (12130.20410) update. I guess I got stuck because my Excel is set to auto update.

    It is driving me nuts for the past 1 week. It bombed on me as I was doing a demo and prodly wanted to show how more than a million rows can be loaded with PowerPivot and PQ Get & Transform Data.

    Please, please help us with a solution Microsoft.

    Monday, December 2, 2019 10:35 AM
  • hello

    posting the query would be appreciated.. .:)

    do you use some external tables to do filering in the query?

    I had a similar problem and it turned out that it was some kind of a bug, but there was a work around.

    Please check my post and check if the work around suggested there is working for you

    https://social.technet.microsoft.com/Forums/en-US/4b5eb556-2d27-4c98-983e-d1cef591d8cb/problem-loading-data-directly-to-data-model?forum=powerquery

    good luck

    Jimmy


    Query it

    Hi Jimmy,

    As DU said, this fails for all queries. I have tried multiple workbooks that have never had any issues -- some freshly made, some old and elaborate, some that were super basic.

    My original post tried to upload a gif that showed this issue for the most basic of query loads (an Excel Table of 1/2/3; A/B/C) but I wasn't allowed to embed any photos or links given I was new here.

    Here's what I am talking about. This is by far the most basic Power Query to Data Model/Power Pivot table one can add and it still hangs.


    I´m literally having this exact same error. Anybody got updates for a possible solution? 

    Thursday, December 5, 2019 7:51 AM
  • oooh... this seems to be a bug indeed.

    Because I'm curious... try to add a Table.Buffer after the IN-statement...

    something like

    in
        Table.Buffer(ChangedType)

    Jimmy


    Query it


    Hi Jimmy,

    Tried your solution running MS Office Professional Plus 2016 (v1911 - 32bit), unfortunately no succes. Thanks for the suggestion though, its a frustrating error. 

    Thursday, December 5, 2019 8:27 AM
  • Thanks Jimmy, I tried the Table.Buffer line but still no luck.

    Thursday, December 5, 2019 6:48 PM
  • I'm having the same issue, Excel 2016 Pro Plus (C2R) version 1911.

    While we're waiting for a fix from Microsoft, I think I found a workaround. I happened to have a machine on which Excel hadn't been updated to 1910/1911. It was still 1909, and Power Query worked in that version.

    So I copied the bin folder of P.Q. that's in:
    "C:\Program Files (x86)\Microsoft Office\root\Office16\ADDINS\Microsoft Power Query for Excel Integrated"

    And I copied it onto my machine which had Excel v.1911, and it seems to work.

    As additional details, I'm thinking the culprit was Version 1910 (Build 12130.20344), released on November 12, 2019. (https://docs.microsoft.com/en-us/officeupdates/update-history-office-2019). They fixed a CVE:

    Information disclosure vulnerability exists in Microsoft Office software when the software fails to properly handle objects in memory. An attacker who successfully exploited this vulnerability could obtain information to further compromise the user’s system. To exploit the vulnerability, an attacker would have to log on to an affected system and run a specially crafted application. The security update addresses the vulnerability by correcting how Microsoft Office handles objects in memory.



    • Edited by dubeg Monday, December 9, 2019 11:43 PM
    Monday, December 9, 2019 11:40 PM
  • Eureka, it fixed the problem! at least until the fix is published by MS thanks
    Tuesday, December 10, 2019 9:28 PM
  • Great news Dubeg & Duyarra.

    Is it possible to upload the Bin folder of 1909 build here or somewhere else as a zip?

    Thanks,

    Vin

    Wednesday, December 11, 2019 12:41 AM
  • Hi, 

    Can you please provide us with your Excel's Product ID and Session ID from a session it reproduces in so we can investigate further?

    Information can be found in "Excel -> Account -> About Excel"

    Thank you,
    Ron.

    Wednesday, December 11, 2019 11:54 AM
  • Hi Ron,

    I upgraded my Excel on my main machine so I could do work, therefore the issue is no longer there. I'll track down the other machine if no one else responds and get you the pertinent information. I have not even tested anything on that faulty machine in many weeks now so, for all I know, the issue could be gone/updated.

    I'll take a look sometime and respond here if you don't get what you need to help you diagnose the issue.

    --

    As an aside, I not only started this thread, but also separately turned on notifications/subscribed and continually check the Alert Me box on any reply and I have yet to receive even one notification when this thread has any responses. The only way I know someone has commented here is if I come to this page randomly and check. I had submitted an email address when I first posted, but when I go into my profile, I don't even see an email field, let alone mine listed.


    • Edited by Wizard-121 Wednesday, December 11, 2019 6:45 PM
    Wednesday, December 11, 2019 6:45 PM
  • Hi Ron, here it is:

    Product ID: 00351-35026-43710-AA642

    Session ID: AC157DF7-49A1-49B5-B733-A52E808F99C4

    Wednesday, December 11, 2019 9:32 PM
  • Hi Vinay,

    Here's a link: https://1drv.ms/u/s!Ap-sAwOg4WG8gslZGdkU5JSXRzCw1g?e=c9YZBJ

    When you decompress it, don't duplicate the root folder, eg. \bin\bin\. Otherwise Excel will give you an error.

    Wednesday, December 11, 2019 9:41 PM
  • Thanks a lot dubeg. Much appreciated!!! You are SUPER AWESOME!
    Thursday, December 12, 2019 5:26 AM
  • @dubeg

    Thanks for sharing the IDs!

    Unfortunately, I couldn't pinpoint the issue for now.

    Any chance you have a workbook that reproduces this issue and you can share with us?

    If not, maybe there are specific steps that easily reproduce the issue?

    Also, is the query loaded to a query table?

    Thanks again!


    Thursday, December 12, 2019 3:36 PM
  • Hello Yehezkel.

    Here's a simple repro:

    - Create a simple list of values such as A, B, C, in any sheet.
    - Create a table out of these values, and select it.
    - Data > (Get & Transform) From Table.
    - Close & Load > Close & Load To...
    - Check [Only Create Connection], and check [Add this data to the Data Model]. Click [Load].

    Notice that the row count displayed is correct, but the query never terminates.



    • Edited by dubeg Thursday, December 12, 2019 9:18 PM
    Thursday, December 12, 2019 9:14 PM
  • Hello,

    I'm having the exact same problem with Excel 2016 Pro Plus.

    'Connection only' and 'load to data model' does not yield any results. Adding the connection only does work but as soon as I add 'load to data model' the query does not load any data.

    Loading to table does work. Adding the data from table to the data model also works. Adding data from Power Pivot also works. But Get&Transform (a.k.a. Power QUery) is bugged atm.

    And since my data has more than 1 million rows, I can not bypass the problem by loading into table.

    Any (Good) News regarding this bug?

    Shlomi

    Saturday, December 14, 2019 10:51 PM
  • I upgraded my Excel on my main machine so I could do work, therefore the issue is no longer there.

    Sorry Ron, for clarification, when you say upgrade you mean you are no longer using Excel2016 (where the issue is solely happening) but you're now using Excel2019 or 365. Is that correct?

    It is the same case here (365). Many of my customers have, and still have , this issue with their machines running Excel 2016 professional, but many of us have decided to shovel in the extra cash to upgrade an continue working.

    What worries me is, that this same problem might happen to us (365 users) in the next iteration, and I'm finding that it is taking painfully too long for the problem to be correctly addressed.

    Again, thanks to dubeg tha found a workaround that I posted to my customers while recommending to turn off automatic updates for now.

    Tx everybody for contributing to this thread,

    DU


    • Edited by duyarra Sunday, December 15, 2019 8:03 PM
    Sunday, December 15, 2019 10:31 AM
  • Hello Ron,

    Product ID:  00332-90002-44767-AA235
    Session ID: 8C9C7E74-B06C-4940-8D07-50263AAF32C9

    I'm having the exact same problem with Excel 2016 Pro Plus


    'Connection only' and 'load to data model' does not yield any results. Adding the connection only does work but as soon as I add 'load to data model' the query does not load any data.

    Loading to table does work. Adding the data from table to the data model also works. Adding data from Power Pivot also works. But Get&Transform (a.k.a. Power QUery) is bugged atm.

    And since my data has more than 1 million rows, I can not bypass the problem by loading into table.

    I've already run the fix office and even re-instlled the office, but it didn't fix it.

    Is there any updated regarding this bug?

    Thanks,

    Shlomi (Israel)


    Sunday, December 15, 2019 3:47 PM
  • I upgraded my Excel on my main machine so I could do work, therefore the issue is no longer there.

    Sorry Ron, for clarification, when you say upgrade you mean you are no longer using Excel2016 (where the issue is solely happening) but you're now using Excel2019 or 365. Is that correct?

    It is the same case here (365). Many of my customers have, and still have , this issue with their machines running Excel 2016 professional, but many of us have decided to shovel in the extra cash to upgrade an continue working.

    What worries me is, that this same problem might happen to us (365 users) in the next iteration, and I'm finding that it is taking painfully too long for the problem to be correctly addressed.

    Again, thanks to dubeg tha found a workaround that I posted to my customers while recommending to turn off automatic updates for now.

    Tx everybody for contributing to this thread,

    DU


    I am not Ron, I am the original poster of the issue. Ron is one of the people who's helping to troubleshoot the issue with Microsoft.

    To answer your question, I have no idea if this problem is isolated to a specific update of Excel 2016 Professional Plus or may end up affecting other versions later. I only know that I've seen two long threads about this issue and every poster has the same edition of Excel. In every other Excel version/edition, I have tried, I have not seen the issue. 

    I was happy to post here to try to troubleshoot the issue and for the good of other community members, but a mission-critical piece of software not working for over three weeks meant I had to quickly move on to continue onward given no one who I consult for cares why my Excel doesn't work. 

    I do hope the issue gets resolved for the good of the community and all users. It's clear it's not isolated and has at least a few dozen people who've either contributed to one of these two threads or clicked "I have the same problem."

    Monday, December 16, 2019 3:41 AM
  • Thanks for your answer Wizard-121, at least we got the workaround. Let's cross our fingers this problem will be sorted out soon.
    Tuesday, December 17, 2019 5:55 PM
  • (Sorry for the delay in my answer. For some reason I haven't got notifications for the new replies.)

    Thanks everyone for clarifying the bug scope!

    Unfortunately, I still can't reproduce it locally even when using the same Excel version mentioned here (12130.20410) and the steps described above. I continue investigating it in more ways.

    Meanwhile, it may helps a lot to debug it if you could please send feedback from inside Excel in a session that reproduced the issue, preferably with a descriptive title mentioning Power Query and data model. (Here is how to do it: https://support.office.com/en-us/article/how-do-i-give-feedback-on-microsoft-office-2b102d44-b43f-4dd2-9ff4-23cf144cfb11#platform=Windows.) For extra helping, posting here the OCV link (you get later by email, if you provided your email in the feedback) will make it even easier and faster to connect them to this issue.

    It'd be great if you could please add more info about the issue:

    • Is Excel still responsive?
    • Is the data model functioning?

    Thanks again!

    Thursday, December 19, 2019 2:59 PM
  • Hi Yehezkel,
    I just did what you suggested. I'll post the OCV link if/when I get it.

    My Excel version is now 12228.20100.
    PowerQuery is now 2.75.5649.941.

    Is it worth mentioning that it's the 32-bit version of Excel/PowerQuery?
    Are you trying to reproduce the issue with the 32-bit version?

    Is Excel still reponsive?
    Yes, it is.

    Is the data model functioning? 
    I'm not sure how to test that. Could you provide instructions?

    Here's a screenshot of what I'm seeing after cancelling & re-trying (Refresh) the never-ending query:
    https://1drv.ms/u/s!Ap-sAwOg4WG8gsla16cmuvLbRi6Imw

    In that state, I can't retry/refresh again.



    • Edited by dubeg Thursday, December 19, 2019 9:38 PM
    Thursday, December 19, 2019 9:35 PM
  • (Sorry for the delay in my answer. For some reason I haven't got notifications for the new replies.)

    Thanks everyone for clarifying the bug scope!

    Unfortunately, I still can't reproduce it locally even when using the same Excel version mentioned here (12130.20410) and the steps described above. I continue investigating it in more ways.

    Meanwhile, it may helps a lot to debug it if you could please send feedback from inside Excel in a session that reproduced the issue, preferably with a descriptive title mentioning Power Query and data model. (Here is how to do it: https://support.office.com/en-us/article/how-do-i-give-feedback-on-microsoft-office-2b102d44-b43f-4dd2-9ff4-23cf144cfb11#platform=Windows.) For extra helping, posting here the OCV link (you get later by email, if you provided your email in the feedback) will make it even easier and faster to connect them to this issue.

    It'd be great if you could please add more info about the issue:

    • Is Excel still responsive?
    • Is the data model functioning?

    Thanks again!

    Is there a place to post the issue about the Technet forums not sending notifications? I have not received a single one on this thread and it appears no one else is either. It's not easy to navigate issues and troubleshoot when there's no notification and one has to randomly check to see if someone has responded every so oten. I'd like to offer that to someone on the Technet side so they can address it.

    -------------

    I finally logged on to the machine I have that still has Office 2016 on it. Here are the specs:

    • Microsoft Excel 2016 (16.0.12228.20322) 64-bit
    • Product ID: 00339-10000-00000-AA361
    • Session ID: 3C8C46B3-2F85-4CD8-825B-EF1E7BA8AF3D

    Recently upgraded to Windows 1909 and re-tested this issue. No change (I wouldn't expect there would be). Office is fully updated as well.

    To answer the questions about is Excel responsive:

    • Excel is fully functional
    • Power Query is fully functional
    • Power Query Load to Table works
    • Power Query Load to Connection works
    • Power Query Load to Data Model hangs and never updates (opening the Data model Window is empty unless there's something in there that didn't originate from Power Query)
    • Excel looks like the screenshot I posted a few weeks ago and that I'm reposting here:

    Even when this "hanging" happens, I can do whatever else I need to do as far as Excel and Power Query go. Ultimately, just to get the annoyance out of the way, I typically cancel the loading and the subsequent "Did not Load" error results.

    This behavior on Excel 2016 across multiple machines has maintained itself over all these weeks even across updates of various kinds. This only happens in Excel 2016. I've now worked with Excel 2019 (on a machine that had this issue) and Excel 365 (on a totally different machine) and neither have had this problem -- either with files that had this issue in 2016 or with brand new files. Every way I've tried on Excel 2016 (new files, old files that used to work, etc) has reproduced this issue 100% of the time.


    • Edited by Wizard-121 Saturday, December 21, 2019 6:22 PM
    Saturday, December 21, 2019 6:21 PM
  • @dubeg

    > Is the data model functioning? 

    > I'm not sure how to test that. Could you provide instructions?

    Insert > PivotTable > Use this workbook's Data Model

    If you can create the Pivot Table and see your data there, the Data Model is working and maybe this is just a cosmetic (even if annoying) issue.


    Many thanks to everyone who filled feedback on this issue. We are looking into them right now.


    Sunday, December 22, 2019 12:57 PM
  • Exactly Same problem here with 

    Microsoft Excel 2016 (16.0.12228.20322) 64-bit in a Windows Server 2016

    And 

    Microsoft Excel 2016 (16.0.12228.20322) 32-bit in a Windows 10

    The only workaround was to over write the DLL's as @Dubeg proposed

    I only have patched the windows 10 Machine, please tell me what to do with the windows server to provide you feedback so you can try to solve this issue. 

    Sunday, December 22, 2019 1:52 PM
  • For anyone not getting notifications, I posted a separate thread and was offered a solution by a Microsoft MVP:

    https://social.msdn.microsoft.com/Forums/en-US/a32f2cdf-8603-47ab-a640-4679e634c2b8/technet-notifications-broken?forum=reportabug


    • Edited by Wizard-121 Monday, December 23, 2019 1:01 AM
    Monday, December 23, 2019 1:00 AM
  • I had the same issue with the Excel 2016 version 1911 as everyone else here. This is definitely a bug, but it will take some time to fix on MS side and we needed an immediate workaround. I tried "transplantation" of the DLLs mentioned here, but it failed and corrupted installation of the Office so I reinstalled the Office and then used standard downgrade procedure. I followed the instructions on the link below and used the config XML below. The Excel was successfully downgraded to 1908 where is this bug not present.

    support.microsoft.com/en-us/help/2770432/how-to-revert-to-an-earlier-version-of-office-2013-or-office-2016-clic

    <Configuration>
    <Updates Enabled="TRUE" TargetVersion="16.0.11929.20254" />
    </Configuration>

    Monday, December 23, 2019 10:16 AM
  • @Yehezkel

    It doesn't seem to be comestic only, the option is greyed out :l

    Tuesday, December 24, 2019 2:12 PM
  • It's now December 27th.  I haven't seen any update to this from Microsoft and when I tried reverting my Office back to the earlier version I got an error code 30029-27.  Is there any hope for a working update to Excel for this issue?  I also tried the other work around of copying the bin folder from the link but all that did was make the New Query, From Database, From SQL Server not do anything.  It just comes back to the main screen and the dialog box to enter the server name never appears.
    Friday, December 27, 2019 7:43 PM
  • Chiming in here, exact same issue on my side, I cant load queries to the data model. Excel 2016, 64 bit, Office 2016 Pro Plus Retail on Windows 10 64 bit. 

    On the 1911 build 12228.20364 C2R

    Quite worrying that its been over a month and there's no fix out for this..

    Saturday, December 28, 2019 3:33 PM
  • Chiming in here, exact same issue on my side, I cant load queries to the data model. Excel 2016, 64 bit, Office 2016 Pro Plus Retail on Windows 10 64 bit. 

    On the 1911 build 12228.20364 C2R

    Quite worrying that its been over a month and there's no fix out for this..

    I am actually quite surprised. This is like if a main/frequently used formula wasn't working. It's such core functionality for some that to not see really anything from Microsoft about ability to reproduce, ETA, etc, is very disheartening (and I'm very pro-Microsoft, mostly).
    Monday, December 30, 2019 11:38 PM
  • Is someone still looking into this?  I don't want this to be forgotten.  As the previous poster pointed out, this is not some small function that hardly anyone uses.  This prohibits being able to bring SQL data into Excel.
    Tuesday, December 31, 2019 7:19 PM
  • It still is unresolved. So far I have my updates settings to "don't update" so I can use dubeg's workaround and keep using Excel with big datasets and data models in power pivot.

    DU

    Thursday, January 2, 2020 11:36 AM
  • Still no update. I'm going to start reporting it frequently on the feedback button so they wake up. It can't be hard to reproduce? 

    Strangely my work laptop, which runs a volume license and that I update excel manually with the updates from the Microsoft website, even though on the December patch, is not affected.

    Anyone know where else we can report this so it receives more attention?

    Thursday, January 2, 2020 6:56 PM
  • I wanted to report the same issue as the one described by Wizard 121 (initial post).

    Below my system configuration:

    MS Win 10 Pro, Version 10.0.18363 Build 18363

    Excel:

    MS Pro Plus 2016 (16.0.12228.20100) 32-bit

    Product ID: 00338-97684-28485-AA947

    Session ID: D35DF5B8-3C5C-4824-827F-74C949130143

    Power Query Version: 2.75.5649.941 32-bit

    I hope Microsoft teams are still investigating, as this issue is blocking for people using the functionality of loading data to the data model. Could you please confirm?

    The behaviour is the same as already reported from other users.

    @MS Team: Please reply and advise.

    Thursday, January 2, 2020 8:13 PM
  • This is still an issue.  I'm getting ready to begin a new position and had planned on using Excel as an initial bridge to begin data analysis and then possibly transition to Power BI.  If Microsoft is not going to find a resolution to this issue I may have to suggest an alternative BI solution.  There has been no communication from Microsoft as to a possible fix or even that they are continuing to look into this.  It's beyond annoying that it has been almost 2 months and no feedback is being provided.
    Saturday, January 4, 2020 11:30 PM
  • Me too I have this problem!!!

    Ivo

    Sunday, January 5, 2020 7:31 AM
  • I also have the problem. Office version: MS Pro Plus 2016 (16.0.12228.20100) 32-bit.

    I am looking forward to having a fix.

    Sunday, January 5, 2020 7:01 PM
  • Hello @YehezkelBernat,

    Were you able to reproduce the error?

    Can we help you with anything i order to get this fixed as quick as possible? 


    Monday, January 6, 2020 1:20 PM
  • I too have this issue :

    Session ID: F91F40C2-C8D2-4D23-9649-83DCADBA3F11
    Product ID: 00339-10000-00000-AA937

    Unfortunately the bin fix doesn't work for me :(.

    Monday, January 6, 2020 2:29 PM
  • Apparently we are not going to get any replies from Microsoft.  Has anyone had any experiences with other BI software such as Tabular or Qlikview?  I'm running out of time and I need to be able to offer alternatives to Excel and Power BI.

    Thanks


    Monday, January 6, 2020 4:52 PM
  • UPDATE:  Apparently the reason we are not getting any response from Microsoft is that the November updated caused bigger problems than just the one we reported here.  Google "Forbes warning issued for millions of microsoft 10 users".

    Here's some info from the article:

    "Picked up by the ever-reliable Windows latest, Microsoft’s Windows 10 November update is causing significant problems by breaking core elements of File Explorer. These include file search, file transfers and being, as one user described, “generally all around terrible”. Windows Latest notes these bugs are “widely reported”. 

    For its part, Microsoft has committed to investigate the issue, though Microsoft senior program manager Brandon LeBlanc has riled some affected users after tweeting “We will look into this but since it’s not a pressing issue, we may not get traction until after the holidays.”

    "It's a very pressing issue, affecting everyone," replied one disgruntled user.

    In the meantime, we can add these File Explorer issues to an inglorious list which - since September - includes BSOD crash reports, boot failures, broken printing, Search and Start Menu bugs, USB and audio problems, screen discoloration, spiked CPU usage and broken Internet connectivity. 

    Microsoft has promised to make fundamental changes to Windows 10 updates but, for an operating system approaching one billion users worldwide, the fruits from this need to arrive sooner rather than later. "

    Monday, January 6, 2020 5:54 PM
  • Does anybody know if we can report this issue at another level/ blog/ site/...then this place, where we would receive an answer from a MS Team with a "real" follow up on the issue?

    It seems that there is not much attention from MS here.

    Thank you for all additional information that would help us to move forward with this problem.

    Monday, January 6, 2020 7:22 PM
  • For those where my /bin/ upload isn't working for whatever reason, rolling back to a previous version seems like a better alternative.

    You can use the method suggested by SpamXBin (support.microsoft.com/en-us/help/2770432/how-to-revert-to-an-earlier-version-of-office-2013-or-office-2016-clic) (does it required C2R?).

    Or, if you have a click-to-run installation of Office (as opposed to what you have when installing Office with .msi package), you can also use this tool, which should already be on your system:

    "C:\Program Files\Common Files\microsoft shared\ClickToRun\OfficeC2RClient.exe"

    Rolling back to a previous version:

    OfficeC2RClient.exe /update user updatetoversion=16.0.12026.20320

    Version History for Office 2016/2019 C2R:

    - Version 1911 (12228.20364) Dec 10
    - Version 1910 (12130.20410) Nov 22
    - Version 1910 (12130.20390) Nov 18
    - Version 1910 (12130.20344) Nov 12
    - Version 1909 (12026.20320) Oct 8
    - Version 1908 (11929.20300) Sept 10

    Use whichever number between parentheses, and prefix it with "16.0.", e.g. "16.0.12228.20364". I went back to the version from Oct 8, in which the issue isn't present.

    For references:

    http://web.archive.org/web/20190419144959/https://blogs.technet.microsoft.com/odsupport/2014/03/03/the-new-update-now-feature-for-office-2013-click-to-run-for-office365-and-its-associated-command-line-and-switches/
    - https://docs.microsoft.com/en-us/officeupdates/update-history-office-2019

    EDIT:
    Don't forget to disable Office updates until a fixed version is released.


    • Edited by dubeg Tuesday, January 7, 2020 4:21 PM
    • Proposed as answer by Diuuugh Thursday, January 16, 2020 8:25 AM
    Tuesday, January 7, 2020 3:57 PM
  • Try this workaround:

    Step 1 - Create your query.  In "Load To", select "Only Create Connection".  Do NOT check "Add this data to the Data Model". 

    This will create a connection only query.

    Step 2 - Insert a new pivot table on a blank tab of your worksheet.  Select "Use an external data source" and select the connection only query you created in step 1.  Check "Add this data to the Data Model".

    This will create a second query which places the results of your connection only query in the Data Model.

    After the new query has loaded, you can optionally delete the tab containing the pivot table.  This doesn't seem to affect either the Data Model or the new query.

    I've tried this in several scenarios and so far, this work-around has worked for me.

    • Proposed as answer by JoanaPat Thursday, January 9, 2020 6:26 PM
    Wednesday, January 8, 2020 4:41 PM
  • Hey, THANKS for posting this! 

    It does work, but in order to not have oddly named tables, you have to go back and delete the Original Query name and rename the (2) Query to just the table name and then right click and select refresh for that Query.  You have to do that for every table you bring into the Data Model using this process and delete the workbook tab.  Then, when you open the Data Model it does have the correct names of the tables you added.  It's a pain in the butt to have to do it this way but at least it gets the data into the Data Model.

    One other thing, when you rename the Query, it tells you the relationships are going to be deleted.  It doesn't appear using this method that the table relationships came over anyway but I didn't honestly look at the diagram view before I deleted the original Query and renamed the 2nd Query that gets created when you use Step 2.

    I am going to try building a few visualizations and see if anything is affected.

    Wednesday, January 8, 2020 9:41 PM
  • Thank you, this workaround is working for me.
    Thursday, January 9, 2020 4:30 PM
  • Hi,

    Thank you so much, this workaround is working!!!!

    Thursday, January 9, 2020 6:26 PM
  • I also wanted to report the same problem as described in Wizard 121 (initial post).

    Below my system configuration:

    MS Win 10 Pro

    Exceel:

     MS Professional 2016 (16.0.12228.20100) 64-bit build version 1912 12325.20288)

     Product ID: 00351-70004-06254-AA667

     Session ID: 042BF9B0-59B8-48B4-8AB4-430C3800D3E4

     Power Query version: 2.76.5678.581 64-bit

    The behavior is the same as previously reported by other users, i.e. data prepared in PQ is not loaded into the prepared model, nor can new objects from PQ be added to the PowerPivot data model in MS Excel.

    Please let me know if the Microsoft team is still analyzing the problem, because it is a priority for implemented business processes (analysis and control).

    The @MS team: Please answer and advise.

    Friday, January 10, 2020 1:45 PM
  • I just tweeted to @OfficeSupport and @msexcel I hope it brings more attention to the issue.

    DU

    Saturday, January 11, 2020 10:33 AM
  • Thank you, still waiting for a clean fix from MS Teams.
    Saturday, January 11, 2020 5:29 PM
  • Thanks this worked for me as well.
    Saturday, January 11, 2020 7:51 PM
  • Many thanks, 

    it worked. 

    Tuesday, January 14, 2020 2:01 PM
  • Excel 2016 V 1912 Build 12325.20298

    I had the same problem loading from SQL which I do frequently. In this case 3000 rows.

    Simple workaround is to load the query, and while it is running incessantly, go to table properties and click refresh.  For me it completes the project.  Does not work if loading to the Data Model, since refresh is grayed out, but adding to the data model after the fact is simple.

    Monday, January 20, 2020 6:30 PM
  • Just FYI, Office Update Version 1912 (Build 12325.20298) did not fix this problem.  Still hangs and you have to use the workaround to be able to load data to the data model.
    Tuesday, January 21, 2020 11:35 PM
  • Is Microsoft going to fix this issue?  Are you just ignoring it, does anyone read these comments or are we wasting our time?
    Thursday, January 23, 2020 1:55 PM
  • This workaround is good at the moment for small model projects. However, for large and more complicated there are chaps when there is a need for additional transformations in PQ and reloading into previously loaded data where we have already defined measures, relations and others.

    Maybe people like Ken Puls, Miguel Escobar, Marco Russo, Alberto Ferrari etc. would help us in Microsoft's interest in the reported problem for obvious reasons?

    Thanks for the solution and your time!
    Friday, January 24, 2020 9:30 AM
  • Two months later and a core function of their Excel Program is not functioning.  Do they not support Excel 2016 anymore?  Are they forcing more constant updates/renewals of their software?  Are they finally addressing severe security issues with their software and it is leading their code to malfunction?  Let me know if you get answers.
    Saturday, January 25, 2020 6:21 PM
  • This is the only contact I've received.  It was sent on Jan 16, but I didn't see it right away.  I replied on Jan 23 and have not heard back.  I did the first thing they asked and of course, got the continuous circle that never completes.  I couldn't find legacy wizards anywhere.

    "Thank you for sending Microsoft feedback. Your input is valuable to us and helps improve our products.


    In order to investigate your feedback, we would like to get some additional information from you. Please reply back to this email with the results of the following tests:
    1. Create a blank query (found under "From Other Sources"), add the text "=1" as a query and load to data model (connection only). Does it work?
      i.e. when opening "Manage Data Model" window, does it display "1"?
    2. Create a connection using one of the legacy wizards (here is how to find them) and load to data model (connection only). Does it appear in "Manage Data Model" windows?

    For your reference, you submitted the following feedback on January 2, 2020:
    This is to supply feedback as requested by Yehezkel Bernat in Question in Power BI > Power Query about Power Query Load to Data Model hanging up in the social technet forum.

    If you have any questions, please reply back to this email.

    Thank you,
    The Microsoft Team

    "

    Saturday, January 25, 2020 8:15 PM
  • Hi,

    worked also for me, thanks.

    Tuesday, January 28, 2020 10:46 AM
  • Same issue with Excel Professional 2016:

    Version 1942 B 12325.20344

    MSO 16.0.12325.20280

    Tuesday, January 28, 2020 12:21 PM
  • As long as this issue is not addressed, I will keep replying so this stays near the top of the forum.
    Thursday, January 30, 2020 1:27 PM
  • Finally, it works!
    Friday, January 31, 2020 7:52 AM
  • Thank you!  It is fixed!
    Friday, January 31, 2020 11:00 PM
  • Thank you!  It is fixed!

    Hi

    Can someone share version and build numbers where this is fixed? Thanks

    Saturday, February 1, 2020 2:01 AM
  • My Office updated tonight Version 1912 (Build 12325.20344) and I can once again choose Add to Data Model from the Close & Load To option in Power Query.
    Saturday, February 1, 2020 2:32 AM
  • My Office updated tonight Version 1912 (Build 12325.20344) and I can once again choose Add to Data Model from the Close & Load To option in Power Query.
    Thanks Robert. I wasn't affected by the issue (I run Office 365) but have a couple of friends who will like the news
    Saturday, February 1, 2020 2:54 AM
  • Hi Lz.

    Seems to be solved in version 2001 b12430.20184

    Saturday, February 1, 2020 11:53 AM
  • Seems to be solved in version 2001 b12430.20184 FYI nothing about that in the Release notes (could be a silent fix...)

    Hi JP

    I thought about you when I saw Robert's reply and I was hoping you would come here because things were not and are still not clear to me: on the MS forum you said you were running v1912 build 12325.20344 and experiencing the issue. Now, Robert says v1912 build 12325.20344 (so the exact same version & build) fixes the problem on his side ???

    @Robert,

    For the sake of clarity could you double-check the version+build you currently run

    Saturday, February 1, 2020 1:18 PM
  • Version 1912 (Build 12325.20344).  I'm certain.

    From earlier in this discussion my Version and build was: Office Update Version 1912 (Build 12325.20298) 



    Sunday, February 2, 2020 1:03 AM
  • Hi Lz.

    I'm pretty sure about the version that I mentioned before. Robert has a Pro Plus while I have a Pro version. It looks as the version and build numbers depend also on the product.

    Sunday, February 2, 2020 10:19 AM
  • Hi Robert & JP (edit your post pic. => email address)

    THANK YOU very much guys, things are a bit more clear. As JP pointed this could be Pro vs Pro Plus. Haven't had a chance yet look at the various versions on the release notes page yet...

    Anyway, I don't think I'm the person who will and should definitively clarify which Home, Pro, Pro Plus… version and build number that addresses the issue. On that thread Ehren said Updates about this issue will be posted there (meaning this thread)
    Versions+Builds you guys refer to are from Jan 30th so it's reasonable to think they simply haven't had the time  to post the information yet

    Sunday, February 2, 2020 11:35 AM
  • Thank you all for reporting the issue and for all the details you have provided.
    As you may have already noticed, we finally found the root cause of it and according to our testing it has been fixed.
    Please restart Excel before trying it again.
    If you aren't using one of the latest versions already, you may have to update your installation first.

    Yehezkel,

    Excel team



    Sunday, February 2, 2020 6:08 PM
  • I've just updated Excel and now it works, it loads the data to the data model without any hiccups.

    Excel version of 2016 professional plus now shows "version2001 compilation 12340.20184

    Thank you Excel team

    DU

    Monday, February 3, 2020 11:59 AM
  • Many thanks. Very frustrating bug (I wouldn't call it that even given it is so pervasive) given it's such core functionality so the wait felt very long, but all is well that ends well. Hopefully we don't see such a wait again.

    Thanks to Duyarra, Yehezkel, and Lz._ for navigating both forums.

    In other news, nothing I have done to try to get notifications working here has worked since my first post, even with the help of the Microsoft MVP in previous posts I outlined here.

    As such, I likely will not use technet again and go to Microsoft Answers or somewhere else. I didn't know this thread had so much activity, until I got a series of alerts on the Answers thread that's the sister to this one.

    Monday, February 3, 2020 4:27 PM