Append just missing extracts

[joshua1990]

Hi experts!

I have a dataflow that combines two different data sources with the same table and structure:

1. SQL Server
2. Sharepoint

The structure for the table is as following:

• Extract DateTime | Article | Value

The first columns display the datetime for the individual extract.

For now I have built a datafow that extracts all 2021-2023 extracts from the sharepoint and adds all 2024 extracts from the SQL server.

Now I would like to built a logic that actually checks if the extracts is already existing in the Sharepoint and if missing it takes it from the SQL server.

How is this possible to build in a Pro Workspace and a dataflow? Maybe without a merge since it takes a loot of time due to the size of the table.

[Anonymous]

I would check by getting the lists of the first columns of each table, and use them in List.Buffer(List.Difference(SharepointTable[DateTime], SQLTable[DateTime]))

Now you can use that on your main query as  a Table.SelectRows(SQLTable, each List.Contains(List.Buffer(NameofIntersectQuery), [DateTime]))

You could also right off the bat just use Table.SelectRows(SQLTable, each not List.Contains(List.Buffer(SharepointTable[DateTime], [DateTime])). This will make a WHERE .DateTime NOT IN (List), but if you have thousands of values in the list, that will eventually fail, hence the first solution.

--Nate

[lbendlin]

Not clear to me what you are trying to achieve. What's the business benefit of the dataflow?  Is this Gen1 or Gen2?