Reply
Macwin
Helper I
Helper I
Partially syndicated - Outbound

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

 

Macwin_0-1648204577308.png

 

 

Sorry I had trouble creating this post. But hopefully its fully complete now and you guys understand the problem im facing.

 

Thanks in Advance. 🙂 

 

 

2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

Syndicated - Outbound

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

View solution in original post

Syndicated - Outbound

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

View solution in original post

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Macwin ,

 

Table 1

vstephenmsft_4-1648535771289.png

Table 2

vstephenmsft_5-1648535778546.png

 

You can use the merge operation in Power Query Editor.

vstephenmsft_0-1648535546040.png

You can choose the join kind you want. Here's an official document for reference.

vstephenmsft_1-1648535570674.png

If you want to display rows with the same Document No in both tables, you need to select Inner kind.

vstephenmsft_2-1648535642594.png

Results:

vstephenmsft_3-1648535676801.png

 

 

 

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
Super User
Super User

Syndicated - Outbound

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

Syndicated - Outbound

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

Syndicated - Outbound

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

Macwin
Helper I
Helper I

Syndicated - Outbound

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 NoTable 2 Document NoMerge Table Value 
-OR-10Table 2 Values 
OR-11 OR-11Table 1 Values
OR-9-Table 1 Values

 

Thanks in Advance

Vijay_A_Verma
Super User
Super User

Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)