- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Table merge with a specific Condition
Hello Guys,
I have a problem and currently dont know how to resolve this problem.
I have two Tables.
Table A = Active Order Lines
Table B = Archived Order Lines
Now we need to merge this two tables together with the following requirement with the merge.
"If the Document No. exists in both Tables only the actives ones should be displayed (Archived ones should be filtered out).
Sorry I had trouble creating this post. But hopefully its fully complete now and you guys understand the problem im facing.
Thanks in Advance. 🙂
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can't do a simple UNION between the tables as there appear to be different columns between the tables, however you can use SELECTCOLUMNS to build a table with the required columns, something like
New Table =
var documentNumArchived = EXCEPT( VALUES(Archived[Document no]), VALUES(Active[Document no]) )
return UNION( SELECTCOLUMNS( Active, column list here),
CALCULATETABLE( SELECTCOLUMNS(Archived, column list here), documentNumArchived)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In the SELECTCOLUMNS you can create a column which specifies which table it comes from, you could then filter on that
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Macwin ,
Table 1
Table 2
You can use the merge operation in Power Query Editor.
You can choose the join kind you want. Here's an official document for reference.
If you want to display rows with the same Document No in both tables, you need to select Inner kind.
Results:
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can't do a simple UNION between the tables as there appear to be different columns between the tables, however you can use SELECTCOLUMNS to build a table with the required columns, something like
New Table =
var documentNumArchived = EXCEPT( VALUES(Archived[Document no]), VALUES(Active[Document no]) )
return UNION( SELECTCOLUMNS( Active, column list here),
CALCULATETABLE( SELECTCOLUMNS(Archived, column list here), documentNumArchived)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the Reply 🙂
I got the table created. Is there a possibilty to filter this table like I mention it in the first post ?
for example as new bool column so i can use it for a visual slicer filter
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In the SELECTCOLUMNS you can create a column which specifies which table it comes from, you could then filter on that
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
sorry, I have tried to insert a Screenshot but cant due a HTML Error
Instead I Have tried to create a example for the cases:
Table 1 Document No | Table 2 Document No | Merge Table Value |
- | OR-10 | Table 2 Values |
OR-11 | OR-11 | Table 1 Values |
OR-9 | - | Table 1 Values |
Thanks in Advance
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please post screenshots of both tables.
How to Get your Question Answered Quickly - https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
06-05-2024 01:43 PM | |||
07-10-2024 12:27 PM | |||
10-13-2023 12:57 AM | |||
03-28-2024 06:28 AM | |||
12-06-2023 06:03 PM |