Simple query won't finish loading data to model in Excel

Question

I have set up a query in Power Query, not complex one. It just loads a table that is previously loaded to the current workbook, add a custom column and filter out some rows, basically. The problem is... it takes forever and never ends with loading the outcome to data model. Why is that?

I have tried restarting Excel, the computer itself, enabling and disabling 'Fast Load' on query properties; unsuccessfully. I noticed that it happens to other files as well, usually when I am using queries from local tables.

After cancelling the operation by hitting ESC, I got this error:

which is weird, since the data source is a table in the same workbook. Table's name is ok.

In the hopes that it may help, please see my M-code below:

let

    Source = Excel.CurrentWorkbook(){[Name="Table_table1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MasterPart", type text}, {"Quantity", Int64.Type}, {"Project", Int64.Type}, {"ProjBin", Int64.Type}, {"WH", Int64.Type}, {"Level", Int64.Type}, {"Level2", Int64.Type}, {"Textbox86", type text}, {"MasterPartNum1", type text}, {"textbox32", type text}, {"MasterPartDesc", type text}, {"MasterLifecycle", type any}, {"MasterRevision", type any}, {"MasterBomQ", type any}, {"Level1", Int64.Type}, {"Textbox57", Int64.Type}, {"Textbox87", type text}, {"MasterPartNum", type text}, {"SubPartNum", type text}, {"UM", type text}, {"Description1", type text}, {"SubPartLifecycle", type any}, {"SubPartRevision", type any}, {"TotSubPartQty", Int64.Type}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Textbox87] <> "Cable Part") and ([MasterPartNum] <> "") and ([SubPartNum] <> "BUY")),

    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "pp_flag", each if List.PositionOf(Table.Column(#"Filtered Rows","MasterPartNum"),[SubPartNum],0) = -1 then "Yes" else "No"),

    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([pp_flag] = "Yes")),

    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"SubPartNum", "Description1", "SubPartRevision", "TotSubPartQty"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"SubPartRevision", type text}}),

    #"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each ([SubPartNum] <> kanban)),

    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"SubPartNum", "Sub Part"}, {"Description1", "Description"}, {"SubPartRevision", "Revision"}, {"TotSubPartQty", "Qty"}})

in

    #"Renamed Columns"

---

Btw, #'Filtered rows2 does not work, but it doesn't throw any error either. So, I believe it's not cause to this issue.

I'm using Office 2016 Pro.

Answer 1

Right-Click the "kanban" query \ choose "Load To..." \ Uncheck "Add to Data Model" \ Load \ Try Again

Maybe do the same with your other query.

Andreas.

Answer 2

Thanks Andrea. However, my kanban query is not set to load to data model . The other one is. When I uncheck that box, and check again it loads after a while. But it doesn't solve the problem because whenever I try to refresh it , the problem remains the same and the query never finishes loading again.

Answer 3

I know about that issue, the problem is the data model, I've had the same problem in the past.

The only stable way I found is to uncheck the data model option.

Andreas.

Answer 4

Hi Leonardo

As you run Excel 2016 Pro check Problems loading to data model from get and transform (power query) with Excel 2016 and Power Query Load to Data Model -- Constantly Stuck/Hanging (your issue is very similar...)

If you're on a version & build where the bug is fixed could you post at least the code of your kanban query, or even better upload and share your workbook on OneDrive, Google drive... (any file sharing service) and post the link here?