Forum Discussion
Append Queries based on data in 1 column
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
7 Replies
joooffice - Not certain, but it seems you want to use incremental refresh?
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?
Hi joooffice
I think this will do what you want. Here is what I did.- Two tables, your CSV file, and a spreadsheet submission
- 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.
- 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
Thanks, but I can't open a PBIX file. Can you copy the steps in text instead?
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.
