Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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 date | No_Header | Customer_No | Amount |
2019-01-02 | 101010 | 30000 | 35 000,00 |
2019-01-02 | 101011 | 10000 | 5 788,00 |
2019-01-03 | 101012 | 20000 | 9 000,00 |
2019-01-03 | 101013 | 20000 | 1 577,00 |
2019-01-04 | 101014 | 10000 | 1 533,00 |
2019-01-10 | 101015 | 20000 | 1 023,00 |
Table: SalesCrHeader
Posting date | No_Header | Customer_No | Amount |
2019-01-01 | 101010 | 30000 | 10 000,00 |
2019-01-01 | 101011 | 10000 | 20 000,00 |
2019-01-02 | 101012 | 20000 | 15 000,00 |
Thanks.
Lisa
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |