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

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? 

    17 hours 25 minutes ago
  • 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. 

    16 hours 49 minutes ago
  • Thanks Jimmy, I tried the Table.Buffer line but still no luck.

    6 hours 27 minutes ago