Reply
Frequent Visitor
Posts: 6
Registered: ‎04-18-2017

Merge problems - incorrect expand

[ Edited ]

Hi all, 

 

I have the following problem. I have 2 tables, which have the following approximate format:

 

Table 1 (selection of columns)

 

Email           Transaction ID  Date              Payment processor   Status

 

john@gmail  0001                01/02/2018   PaymentYellow          Paid

john@gmail  cd_03A            01/03/2018    PaymentBlack           Paid

john@gmail  cd_03B            01/04/2018    PaymentBlack           Paid

john@gmail  cd_03B            01/04/2018    PaymentBlack           Refund

 

Table 2 (all columns): 

 

Email           Transaction ID  Date              Payment processor   Transaction number

 

john@gmail  0001                01/02/2018   PaymentYellow          0

john@gmail  cd_03A            01/03/2018    PaymentBlack           1

john@gmail  cd_03B            01/04/2018    PaymentBlack           2

 

 

The transaction number in table 2 is correctly calculated after the following indexing: 

 

#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Email"}, {{"Count", each Table.AddIndexColumn(_,"Index2",0,1), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"PaymentProcessor", "Transaction Id", "Date", "Index2"}, {"PaymentProcessor", "Transaction ID", "Date", "Transaction number"}),

 

The problem happens after the merge. After merging based on any combination of keys, trimmed, cleaned, transformed all to text, the transaction number, in around 25% of cases is not correctly transfered from Table 2 to Table 1 (left outer merge, with table 1 being on the left - I've tried multiple join options). No duplicate rows, most numbers get transported, but a significant part of them get incorrectly transported, with no obvious (to me) pattern, until now. 

 

Please help, I've been losing a lot of sleep over this. 

 

The expected result would be: 

 

Table 1 (after merge and expand) 

 

Email           Transaction ID  Date              Payment processor   Status   Transaction number 

 

john@gmail  0001                01/02/2018   PaymentYellow          Paid           0

john@gmail  cd_03A            01/03/2018    PaymentBlack           Paid           1

john@gmail  cd_03B            01/04/2018    PaymentBlack           Paid           2

john@gmail  cd_03B            01/04/2018    PaymentBlack           Refund       2

 

 

 

Highlighted
Community Support Team
Posts: 2,979
Registered: ‎02-06-2018

Re: Merge problems - incorrect expand

Hi @nazdravanul,

 

Assuming that you have added a index column, then you only need to select Table 1 and click Merge Queries like below.

 

Untitled.png

The detail steps you could have a reference of my attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Frequent Visitor
Posts: 6
Registered: ‎04-18-2017

Re: Merge problems - incorrect expand

[ Edited ]

Thank you for the reply. I've been using PowerBI for years, I know how to use the UI to do an Expand. That is not my problem. After expand part of the data is incorrectly ported from Table 2 to Table 1 - see details in my post. 

Community Support Team
Posts: 2,979
Registered: ‎02-06-2018

Re: Merge problems - incorrect expand

Hi  @nazdravanul,

 

I'm not very clear abourt your issue based on your description and I cannot reproduce your issue in my side. Could you share a screenshot about your issue?

 

If it is convenient, could you share a dummy pbix file which can reproduce this issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Posts: 3,944
Registered: ‎01-14-2017

Re: Merge problems - incorrect expand

Hi,

 

Please share the link from where i can download your PBI file.

Frequent Visitor
Posts: 6
Registered: ‎08-04-2017

Re: Merge problems - incorrect expand

@nazdravanul Did you find a solution to this? I think I am having the same problem? When I click on a table to preview before expanding the data is correct, but it changes after the expand?