Append Queries based on data in 1 column

[joooffice]

I have a csv file of data of submissions through an online form. The csv file contains all the data ever submitted via the form.

On a weekly basis I need to process the data on the csv file using the query and then manually enter some data in new columns in each row. 

When i export the data the following week and run it through the query, I lose the manally entered data. I would like to only process newly submitted data and add it to my spreadsheet - I know i can do this with an append query but how do I only append based on a submission date later then already exists in my spreadsheet?

In the example data below - columns A B C D are in the CSV files and I have to manually populate 'Confirmation Sent' and'Reply'

I have processed the bottom 3 rows but since then there have been all the submissions above ,

Submission Date	Person	Waiting List	Desired frequency	Confirmation Sent	Reply
29/07/2020 20:18	Shaun	No	Yes
29/07/2020 19:03	Janine	No	Yes
29/07/2020 16:39	Robin	Yes	Yes
29/07/2020 11:51	Richard	Yes	Yes
29/07/2020 10:09	Henry	No	No
29/07/2020 09:32	Lester	Yes	Yes	Y	Yes
28/07/2020 22:24	David	Yes	Yes	Y	No
28/07/2020 17:54	Ruth	No	No	Y	Yes

Is there a way to either build a query that removes all rows from my csv with submission dates/times that already exist in my spreadsheet or 

I need 

I periodically need to process the data from my csv file 

[edhans]

Hi @joooffice 
I think this will do what you want. Here is what I did.

1. Two tables, your CSV file, and a spreadsheet submission
2. I made a list of only the dates/times in the CSV file that do not exist in the spreadsheet file and kept those rows.
3. Appended the spreadsheet data.

See my PBIX file here. Go to the Transform Data screen to see what I did step by step.

It turns this:

into this

[joooffice]

Thanks, but I can't open a PBIX file. Can you copy the steps in text instead?

[edhans]

Full code is here:

// CSV File
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLUNzDXNzIwMlAwMrAytFDSUQrOSCzNA9J++UAiMrUYSCqAcawOinpDSysDY6CEV2JeZl4qMRrMrIwtgRJB+UmZeXCl+DQYWpkagjRkJmckFqUQpcXAygBkh0dqXlElzE1gAqtyA0srYyOghE9qcUlqEZoFIARWb4EIIyMrIxOguEtiWSa6e7AoNzS3MgUpDyotyUBxDFhtLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Submission Date" = _t, Person = _t, #"Waiting List" = _t, #"Desired frequency" = _t, #"Confirmation Sent" = _t, Reply = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Submission Date", type datetime}}, "en-BS"),
    #"Dates to Keep" = List.Difference(#"Changed Type with Locale"[Submission Date],Spreadsheet[Submission Date]),
    #"Filter for no submission rows" = Table.SelectRows(#"Changed Type with Locale", each List.Contains(#"Dates to Keep", [Submission Date])),
    #"Appended Query" = Table.Combine({#"Filter for no submission rows", Spreadsheet})
in
    #"Appended Query"

// Spreadsheet
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLUNzDXNzIwMlAwsLQyNlLSUfJJLS5JLQIyIlOLESSUFasD1GMB12NkZGVkApRySSzLTMGixS8fXYehuZUpSEdQaUkGRAGUgNsQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Submission Date" = _t, Person = _t, #"Waiting List" = _t, #"Desired frequency" = _t, #"Confirmation Sent" = _t, Reply = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Submission Date", type datetime}}, "en-BS")
in
    #"Changed Type with Locale"

You should be on the July 2020 desktop though.

[joooffice]

Thanks Edhans - I understand the logic but I keep getting the error message at the Dates to Keep row

Expression.Error: We cannot apply an index of type Text.
Details:
Value=[Table]
Index=Submission Date

[edhans]

I just copied my code above in to a CSV and Spreadsheet query and it works fine. I don't really know what to say at this point other than try installing the July 2020 version of Power BI desktop and looking at the PBIX I shared.

You could share your PBIX with me and I may be able to see the issue. I wouldn't be able to share it back though fixed as my desktop app will upgrade to the July 2020 format.

Perhaps try redoing the code again using the M code directions below. Note that it is 2 queries, one called CSV and one called Spreadsheet.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Greg_Deckler]

@joooffice - Not certain, but it seems you want to use incremental refresh?

[joooffice]

Thanks, having read up on it, an incremental refresh looks exactly what I want but it only availalbe on pro accounts which I don't have. Is there a way around this?