Forum Discussion
Anti join custom query
Hi community, in ths time., I have two tables A y B, the tbale A have some columns are ID_CITY, ID_STATE, ID_COUNTRY, ID_YEAR, DESCRIPTION_MESSAGE, COUNT_PEOPLE, the other table B the columns are: ID_CITY, ID_STATE, ID_COUNTRY, ID_YEAR, ANNUAL_INCOME, AVERAGE_HEIGH, PERFORMANCE_SCORE,
So with the anti right join (ID_CITY, ID_STATE, ID_COUNTRY, ID_YEAR) in power is get the rows than exists in table B but they don't in table A, so with the result create a "new records" with the same structure that table A and append this records in A.
is this possible?
custom function required?
thanks in advance.
Here is an example of a right anti join with the resulting row being appended to the original table A.
let //table A sample data Table_A = #table( type table [ID_CITY=nullable text, ID_STATE=nullable text, ID_COUNTRY=nullable text, ID_YEAR=nullable number, DESCRIPTION_MESSAGE=nullable text, COUNT_PEOPLE=nullable number], { {"Atlanta", "GA", "US", 2025, "Row 1", 5}, {"Los Angeles", "CA", "US", 2024, "Row 2", 15}, {"Boise", "ID", "US", 2026, "Row 3", 2} } ), //table B sample data Table_B = #table( type table [ID_CITY=nullable text, ID_STATE=nullable text, ID_COUNTRY=nullable text, ID_YEAR=nullable number, ANNUAL_INCOME=nullable number, AVERAGE_HEIGHT=nullable number, PERFORMANCE_SCORE=nullable number], { {"Atlanta", "GA", "US", 2025, 62534, 1.72, 6}, {"Los Angeles", "CA", "US", 2024, 58746, 1.68, 7}, {"Portland", "OR", "US", 2026, 71354, 1.75, 8} } ), //right anti join between table a and table b, selecting only table b columns anti_rows = Table.SelectColumns( Table.Join( Table.PrefixColumns(Table_A, "A"), {"A.ID_CITY", "A.ID_STATE", "A.ID_COUNTRY", "A.ID_YEAR"}, Table_B, {"ID_CITY", "ID_STATE", "ID_COUNTRY", "ID_YEAR"}, JoinKind.RightAnti ), {"ID_CITY", "ID_STATE", "ID_COUNTRY", "ID_YEAR"} ), //add anti rows to table a combine_tables = Table.Combine( { Table_A, anti_rows } ) in combine_tables
5 Replies
Here is an example of a right anti join with the resulting row being appended to the original table A.
let //table A sample data Table_A = #table( type table [ID_CITY=nullable text, ID_STATE=nullable text, ID_COUNTRY=nullable text, ID_YEAR=nullable number, DESCRIPTION_MESSAGE=nullable text, COUNT_PEOPLE=nullable number], { {"Atlanta", "GA", "US", 2025, "Row 1", 5}, {"Los Angeles", "CA", "US", 2024, "Row 2", 15}, {"Boise", "ID", "US", 2026, "Row 3", 2} } ), //table B sample data Table_B = #table( type table [ID_CITY=nullable text, ID_STATE=nullable text, ID_COUNTRY=nullable text, ID_YEAR=nullable number, ANNUAL_INCOME=nullable number, AVERAGE_HEIGHT=nullable number, PERFORMANCE_SCORE=nullable number], { {"Atlanta", "GA", "US", 2025, 62534, 1.72, 6}, {"Los Angeles", "CA", "US", 2024, 58746, 1.68, 7}, {"Portland", "OR", "US", 2026, 71354, 1.75, 8} } ), //right anti join between table a and table b, selecting only table b columns anti_rows = Table.SelectColumns( Table.Join( Table.PrefixColumns(Table_A, "A"), {"A.ID_CITY", "A.ID_STATE", "A.ID_COUNTRY", "A.ID_YEAR"}, Table_B, {"ID_CITY", "ID_STATE", "ID_COUNTRY", "ID_YEAR"}, JoinKind.RightAnti ), {"ID_CITY", "ID_STATE", "ID_COUNTRY", "ID_YEAR"} ), //add anti rows to table a combine_tables = Table.Combine( { Table_A, anti_rows } ) in combine_tablesNo custom queries need to be written.
1. Perform a right anti-join between Table A and Table B to create a new queryC.
2. Retain only the result column in query C, then expand the common column within that result column.
3. Merge Table A into query C. Do not merge query C into Table A, as this will cause a circular reference error.
Thanks ZhangKun jgeddes sannavajjala I have good samples to testing.. thanks in advance
Hi,
Yes, this is definitely possible, and you typically don't need a custom function.
A common approach would be:
- Use a Right Anti (or Left Anti, depending on which table is primary) join to identify records that exist in Table B but not in Table A based on ID_CITY, ID_STATE, ID_COUNTRY, and ID_YEAR.
- Take the resulting rows from Table B.
- Keep only the key columns that exist in Table A.
- Add the remaining columns from Table A (DESCRIPTION_MESSAGE, COUNT_PEOPLE) with default values such as null, 0, or whatever makes sense for your scenario.
- Reorder the columns to match the structure of Table A.
- Append the new rows to Table A.
This can all be done with standard Power Query transformations. No custom function is required unless you need more complex logic for populating the missing columns.
In summary, your process would be: Anti Join → Create rows with Table A's structure → Append to Table A. That's a very common Power Query pattern for keeping a master table synchronized with new keys found in another source.
Thanks & Regards,
Manoj Annavajjala
Hi Peter_23,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to sannavajjala & ZhangKun & jgeddes for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
