cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LisaB
Helper II
Helper II

Combinig two tables - duplicates in keys

Hi,

 

I have two tables, one sales invoice header and one sales credit memos, that I would like to combine into one. However, the same document number can exist in both tables meaning I will have "false" duplicates in my merged table. How would you suggest to handle this?

 

Many thanks.

 

Lisa

1 ACCEPTED SOLUTION

Hi @LisaB,

Add a column to each table with the type of document in one INV on the other CR then do an append of both tables to join them this will give a line per document.

If you don't need to have the document type identification then just use the append.
Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

Hi @LisaB ,

 

Looking at the description you have your Sales invoice header should have a unique ID that links to the sales credit memos, you must check if it makes more sense to use the left join, right join or full join while merging the two tables, also the order that you use your tables to merge is important.

 

But without any specific examples is hard to give you an answer please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi  @MFelix ,

 

Thank you for your reply.

 

I have attached some example data - one tab for Sales Inv Header and one tab for Sales Cr Header. Since the two document types were using two different number series that were so much alike they "collided" in the end.

 

What I would like to do is to merge these two tables into one table where invoice 101010 should be one entry and credit memo 101010 should be another line. One idea I have is to put a prefix in front of the document number, i.e. INV101010 and CR101010, but that means I won't be able to use document number in another relationsships. 

 

Table: SalesInvHeader

 

Posting dateNo_HeaderCustomer_NoAmount
2019-01-021010103000035 000,00
2019-01-02101011100005 788,00
2019-01-03101012200009 000,00
2019-01-03101013200001 577,00
2019-01-04101014100001 533,00
2019-01-10101015200001 023,00

 

Table: SalesCrHeader

 

Posting dateNo_HeaderCustomer_NoAmount
2019-01-011010103000010 000,00
2019-01-011010111000020 000,00
2019-01-021010122000015 000,00

 

Thanks.

 

Lisa

Hi @LisaB,

Add a column to each table with the type of document in one INV on the other CR then do an append of both tables to join them this will give a line per document.

If you don't need to have the document type identification then just use the append.
Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!