Conditional table combine

[83dons]

I currently am combing a current staff table with a leavers table (last 2 years). They both have identical columns and types. At the moment I am just using a simple 'Append Queries as New' option and picking both tables. 

= Table.Combine({Current, Leavers})

However, I would prefer to only combine them if the Leavers table has unique Role-Numbers that are not in the Current table (staff may have left and come back to the same role so will just disregard this info and defauly to the current record). Role-Number is the unique identifier for each role in the table and is in both tables in a column of that name.

Is it possible to do a table combine with this condition that Role-Number must be unique with the Current table being the primary one (ie would retain that record if Role_Number is also in the leaver table).

I think this might be an easier way rather than trying to remove the duplicates further down the line.

I have a feeling it should be a simple as to add a relationship between the two tables perhaps and that would govern how the combine works? Not sure though.

[83dons · Accepted solution]

Hi thanks for all the replies, I have found an alterntive way of doing this that works - please see Re: Removing rows where there are duplicate fields - Microsoft Fabric Community.

[Ashish_Excel]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[ryan_mayu]

@83dons 

this is the sample data I used for testing

maybe you can try remove duplicated to remove the duplicated rolenumber

or  you can add one step in the leavers table 

= Table.SelectRows(#"Changed Type",(x)=> not List.Contains(Current[role_number],x[role_number]))

then combine the table again.

pls see the attachment below

[pankajnamekar25]

Hello @83dons 

try this in Power Query , adjust it in advance editor

let

    CurrentUnique = Table.Distinct(Current, {"Role-Number"}),

    LeaversUnique = Table.Distinct(Leavers, {"Role-Number"}),

    LeaversFiltered = Table.SelectRows(

        LeaversUnique,

        each not List.Contains(

            CurrentUnique[Role-Number],

            [Role-Number]

        )

    ),

    Combined = Table.Combine({CurrentUnique, LeaversFiltered})

in

    Combined

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[83dons]

@pankajnamekar25 I got to this stage but the final table seems huge it is timing out on just counting the number of rows? Are you sure this isnt creating something with a loop too big? Curently at 700MB and still loading the rows and the original files are under 5MB in size so this cant be right for the new table. I am worried this file is far bigger than it should be if I cant even scroll to the bottom of it!

The first two unique tables generate ok its the leavers filtered one that is timing out.