cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nazdravanul Frequent Visitor
Frequent Visitor

Merge problems - incorrect expand

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

 

 

 

14 REPLIES 14
Community Support Team
Community Support Team

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.
nazdravanul Frequent Visitor
Frequent Visitor

Re: Merge problems - incorrect expand

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
Community Support Team

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
Super User

Re: Merge problems - incorrect expand

Hi,

 

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

roscoegray Frequent Visitor
Frequent Visitor

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?

normang Frequent Visitor
Frequent Visitor

Re: Merge problems - incorrect expand

I have the same problem. All data are wrong.

swpbi Frequent Visitor
Frequent Visitor

Re: Merge problems - incorrect expand

I've recently encounterd a similar problem.  Through my own testing, the error (where values are switched) appears to be triggered when a merge is expanded AFTER a grouping event has been conducted in Power Query. 

 

I went so far as to limit my data fields to just 1 row and found the error still occurring.  While the error is observed in the merge/expand step - the symptom appears to be triggered when merges are placed after a grouping event.  

(The grouping event is not pictured - and additional transformations were conducted between the grouping event and this pictured merge)  The errors impacted a subset of rows (less than 10%).  Below I've singled out one of the changed rows to try and problem solve what might be causing it. 

Pic1.pngMerging 1 Row to 1 Row

Pic3.pngPay attention to the values in "First Date"Pic4.png

 

I can reproduce this readily and it seems like a pretty big issue.  I've been using Power BI since 2015. 

userpbi55 Frequent Visitor
Frequent Visitor

Re: Merge problems - incorrect expand

I have this problem as well using Power BI Desktop May 2019 Report Server. Group By before merge => random expand results for whole number/integer data type.

luisamboni Frequent Visitor
Frequent Visitor

Re: Merge problems - incorrect expand

any solution?

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 286 members 2,704 guests
Please welcome our newest community members: