Forum Discussion

JontySchulz's avatar
JontySchulz
Frequent Visitor
7 years ago
Solved

Expanded data incorrect when merging two queries

Hi All,

 

I've got a problem where I've merged queries, and yet somehow when I expand the data the data that is output comes from a different query... not the one I originally merged to! 

 

The source query has a column (Design Package) which it shares with both the query I merge with and the query that the data ends up coming from. 

 

My coding at the merge queries step looks like this, where the table being merged with is "BaselineStartDate":

let
Source = Table.Combine({DataConversionCostA, DataConversionCostB}),
#"Merged Queries" = Table.NestedJoin(Source,{"Design Package Name"},BaselineStartDate,{"Design Package Name"},"BaselineStartDate",JoinKind.LeftOuter),
#"Expanded BaselineStartDate" = Table.ExpandTableColumn(#"Merged Queries", "BaselineStartDate", {"Earliest Date"}, {"BaselineStartDate.Earliest Date"})

in

#"Expanded BaselineStartDate"

 

However, the data that results from expanding comes from a separate query called "UpdatedStartDate".

I know this because I've cross-checked the date which is expanded with both queries and always the value represents that from the "UpdatedStartDate" query, not the one I merged with: "BaselineStartDate".

 

Does anyone have any idea why this would be?

Seems like a basic merging step so not sure how it could be wrong.

 

Thanks for your help! 

 

  • JontySchulz's avatar
    JontySchulz
    7 years ago

    Hi v-lili6-msft,

     

    Thanks for your response.

    I was able to get around the error by putting an additional step in the "BaselineStartDate" query. It seemed that somehow the merge was occuring prior to the last "filter" step in this query, hence why the expanded values were different to what I was seeing in the source query. When I put this extra step (which was just a dummy "remove errors" step), it seemed to fix the problem and the numbers in the merged query matched the source query.

     

    Not sure why this was happening and recognise that my solution is only a temporary work-around, but seems to have fixed it for me... for now.

     

    Cheers,

    Jonty

8 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support

    HI, JontySchulz

         I have tested on my side, but not reproduce the issue. and we need more detail steps for us. It seems that just merge two table by column Design Package Name and then expand the data.

    Please share your sample pbix or some data sample and expected output for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

     

     

    Best Regards,

    Lin

    • JontySchulz's avatar
      JontySchulz
      Frequent Visitor

      Hi v-lili6-msft,

       

      Thanks for your response.

      I was able to get around the error by putting an additional step in the "BaselineStartDate" query. It seemed that somehow the merge was occuring prior to the last "filter" step in this query, hence why the expanded values were different to what I was seeing in the source query. When I put this extra step (which was just a dummy "remove errors" step), it seemed to fix the problem and the numbers in the merged query matched the source query.

       

      Not sure why this was happening and recognise that my solution is only a temporary work-around, but seems to have fixed it for me... for now.

       

      Cheers,

      Jonty

      • alfranco17's avatar
        alfranco17
        Advocate I

        Had the same problem. It was driving me nuts. 

         

        In the end, I took your advice, added a "Remove errors" to the lookup query, and the error went away.

  • I just ran into this as well. Thanks to your post, I added the "remove errors" steps and that fixed it. Not logical, but I'll take it.

     

    Thanks for posting, this was driving me crazy!

    Angie

  • It's now LATE 2014, 4-1/2 years after the last post and 6 years since first instance.  How does one get this into the hands of MS engineers to drive towards a resolution?