locked
Query Failure | Merges on Index generated with Number.RandomBetween RRS feed

  • Question

  • Hi PQ Experts:

    I have an interesting dilemma.

    Outline of Issue:

    • I have approx. 7 dimensional tables
    • Each dimensional table has an index column (1..50), for example
    • I am using these 7 dimensions to construct a "randomized" fact table (say, maybe 50K rows)
    • The core construction of the fact table needs to be a primary key, and 7 foreign key columns

    Methodology:

    • Used List.Generate to stretch out an index for the 50K rows
    • Built a bunch of foreign key columns via Table.AddColumns and a type of Number.RandomBetween function (which calls in, for a specific dimension, a random index number for that dimension)
    • USE SOME OF THOSE NEW FOREIGN KEY COLUMNS AS A BASIS FOR MERGE ACTIONS (TO BRING IN THE APPROPRIATE DIMENSIONAL INFORMATION)

    Issue:

    • This approach works sweetly in the query editor - the preview looks great and no issues were encountered
    • When trying to load the tables (this is in PowerBI Desktop), I get the "not enough enumerations to complete" error

    Thesis:

    • My hypothesis is the CAPITALIZED BULLET about 4 lines above is causing the issue - and that Power Query is having trouble performing any merges on columns that have been previously calculated using the Number.RandomBetween

    I have done some reading by ImkeF and others and understand that Number.Random results cannot be "locked" like in Excel with Paste Special Values.  

    I have tried to break the query out into separate queries (splitting it every time there is such an action as described above).  This has failed.  I have also tried to utilize Table.Buffer.  This has timed out (at least, I have shut it down after 15+ minutes of the spinners).

    I don't want to replicate in SQL - are there any other ideas on how to contain this?  The bizarre thing is, it loads great in Preview in the Query Editor!

    Thanks to all...

    Jake


    • Edited by Jake Burns Wednesday, January 2, 2019 5:43 PM Clean
    Wednesday, January 2, 2019 5:41 PM

Answers

  • Hi Jake,

    I have no further insight on the behaviour you've described.

    Save bet would be to generate the numbers in PQ -> load them to Excel -> link that table back to PQ as a source for all your keys.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, January 4, 2019 10:36 AM

All replies

  • Hi Jake,

    I have no further insight on the behaviour you've described.

    Save bet would be to generate the numbers in PQ -> load them to Excel -> link that table back to PQ as a source for all your keys.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, January 4, 2019 10:36 AM
  • Hi Jake. Is it possible that index references are being generated to indexes which don't actually exist? Maybe this is happening down farther in the data, beyond the rows PQ is showing in the Query Editor.

    Ehren

    Friday, January 11, 2019 12:17 AM