none
Reference Query - different table RRS feed

  • Question

  • Hi All

    Today i experienced an issue with referencing an existing query.

    I have a query to a product table which i connect to with SQL. This table is fine and i added some steps and loaded it into the data model of Power Bi Desktop. In the data view i can select the main column of products and i see all unique product codes 41,902  rows loaded and 41,902 distinct values.

    Now when i reference this query (no steps added) and load it and do the same process there are lots of duplicate product codes? is this a visual bug or has it actually changed the data? 41,903 rows loaded , 40,696 distinct values?

    also, i cannot create a relationship with this table as it produces many to many

    thanks

    Mike

    Tuesday, March 21, 2017 11:54 AM

Answers

  • Could you go to File > Help > About, click "Copy session diagnostics to clipboard", then send it in an email to pbidesfb@microsoft.com and ask them to pass it along to Taylor Clark. There must be something interesting about the query you're referencing that's causing this. 

    Aside from that, try adding a step to the referenced query with the following formula:

    = Table.RowCount(Table.Distinct(Source, {"Code"})) = Table.RowCount(Source)

    If that says "true", then the issue is somewhere during the load to AS in the modeling layer. If that's false the issue is with the query

    Wednesday, March 29, 2017 5:45 PM

All replies

  • Hi Mike. If the second query is simply referencing the first, there shouldn't be any difference in the number of rows loaded. Where are you seeing the duplicates? In the Query Editor? In the Data view?

    Some screenshots would be helpful, if you're able to provide them.

    Thanks,
    Ehren

    Tuesday, March 21, 2017 11:40 PM
    Owner
  • Hi Ehren

    They appear in data view.

    Screen of original table.

    reference query

    view data in data view of second table.

    Hope this helps

    Mike


    Bi Desktop: Version: 2.44.4675.521 64-bit (March 2017)
    • Edited by MikeManwaring Wednesday, March 22, 2017 11:07 AM added version
    Wednesday, March 22, 2017 7:41 AM
  • Mike,

    My hunch is that this is a visual bug only. If that's the case, AS will allow you to create the relationship. Could you try creating it and let me know if it works?

    Taylor Clark

    Monday, March 27, 2017 6:07 PM
  • Hi Taylor

    Unfortunately this is not a visual bug as you cannot create a relationship

    Regards

    Mike

    Tuesday, March 28, 2017 11:07 AM
  • A few things to try to narrow down the cause:

    1. Unload the ProductExtract (2) query via right-click -> uncheck Load to Report. Apply changes. Re-check load to report. Any change the number of rows and unique values?

    2. Duplicate the ProductExtract query and load it to the model. Does it have the proper number of rows and unique values? 

    3. Copy the queries into a new PBI Desktop instance and load them to the model. Any change?

    Tuesday, March 28, 2017 5:18 PM
  • Hello

    I copied the original query into a new file.

    The first instance of the reference query again had duplicates.

    I unloaded the referenced query etc reloaded it and checked it in data view, i can see no data and it reports 2 rows? so it is more broken that before :(

    thanks Mike

    Wednesday, March 29, 2017 8:24 AM
  • Could you go to File > Help > About, click "Copy session diagnostics to clipboard", then send it in an email to pbidesfb@microsoft.com and ask them to pass it along to Taylor Clark. There must be something interesting about the query you're referencing that's causing this. 

    Aside from that, try adding a step to the referenced query with the following formula:

    = Table.RowCount(Table.Distinct(Source, {"Code"})) = Table.RowCount(Source)

    If that says "true", then the issue is somewhere during the load to AS in the modeling layer. If that's false the issue is with the query

    Wednesday, March 29, 2017 5:45 PM