Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
2 years ago

Append just missing extracts

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.

 

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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