Forum Discussion
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. 🙂
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) )In the SELECTCOLUMNS you can create a column which specifies which table it comes from, you could then filter on that
6 Replies
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
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) )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
In the SELECTCOLUMNS you can create a column which specifies which table it comes from, you could then filter on that
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.
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
