Forum Discussion
EXCEPT for columns
Hi,
I have two tables, t1 and t2, each of them containing multiple, partially different, columns. One of the columns, let's call it A, exists in both tables, but the values of the column A are not completely the same in both tables. Now, I would like to create a new table, t3, with only one column - A - which would contain values from A in t1 which cannot be found in A in t2, something like an INNER JOIN, speaking in SQL terms. How do I proceed?
I was thinking about the EXCEPT statement, but it can be applied only on complete tables. Another idea is to duplicate the tables, delete the unnecessary rows, so that only column A remains and then apply the EXCEPT statement on these duplicates of t1 and t2 but there must be some simpler way. Also, maybe it's worth mentioning that each of the original tables comes from different data sources - csv and SQL Server, so that I have no option to run an inner join earlier in the pipeline.
You can create a measure along the lines of:
A only in t1 = COUNTROWS ( EXCEPT ( VALUES ( 'Table 1'[A] ), VALUES ( 'Table 2'[A] ) ) )(The function VALUES returns a table of unique values-including blank rows- of the column within)
Then create a table visual with the field from Table 1[A], add the measure to the filter pane and set the value to = 1
If you need a physical table, use :
A only in t1 = EXCEPT ( VALUES ( 'Table 1'[A] ), VALUES ( 'Table 2'[A] ) )
2 Replies
You can create a measure along the lines of:
A only in t1 = COUNTROWS ( EXCEPT ( VALUES ( 'Table 1'[A] ), VALUES ( 'Table 2'[A] ) ) )(The function VALUES returns a table of unique values-including blank rows- of the column within)
Then create a table visual with the field from Table 1[A], add the measure to the filter pane and set the value to = 1
If you need a physical table, use :
A only in t1 = EXCEPT ( VALUES ( 'Table 1'[A] ), VALUES ( 'Table 2'[A] ) )You could use a Merge Queries as New option in Power Query to run an inner join.
https://learn.microsoft.com/en-us/power-query/merge-queries-overview
I think you will have to end up duplicatig one of the tables though.
