Table merge with a specific Condition

[Macwin]

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. 🙂 

[johnt75 · Accepted solution]

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)
)

[Anonymous]

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.

[johnt75 · Accepted solution]

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)
)

[Macwin]

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

[johnt75 · Accepted solution]

In the SELECTCOLUMNS you can create a column which specifies which table it comes from, you could then filter on that

[Macwin]

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

[Vijay_A_Verma]

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