Power Query for Excel uploads to SharePoint

[Syndicate_Admin]

Hi Community,

Not really sure if I should be putting this query to SharePoint, Power Query, Power Apps, or all three.

The scope

The essence of the challenge I'm confronted with is to upload 3000 lines of Excel data to a SP list, then use a Power App to pull up selected data from the list, then permit the user to add further data to additional columns in the list via a Power App. 

Where Power Query comes in

Since the uploaded data is performed batch wise, the SP list will have to be updated from time to time. Since the SP list will be pulling data from two sources (the batch Excel upload and the Power App UI), the two will have to be merged. I can think of no better way than Power Query.

Question

Will SP permit Power Query to perform inline operations (merging, adding, subtracting, etc) or would this have to be done off SP?  ATM I can only envisage the process of merging data off the SP platform. So the SP list would be downloaded and then merged with the next batch upload - the merged list to replace the SP list. This doesn't sound terribly efficient, so I was wondering if there is a better way? Please bear in mind my skill level is limited to Power Query, with some Power Apps and Power Automate.

Thanks in advance.

[Syndicate_Admin]

Hi @Syndicate_Admin 

The work flow involves 2 lists ( I will call them List A and B for simplicity)

List A is a spreadsheet containing 4 columns (illustrated below after being uploaded as a SP list, showing grouped by the unique Offering code. )

The only  column that does not necessarily contain data is Attendance. The Offering code is a unique course code against which an evaluation is made in which further columns may be added with data via the app interface shown below.

One of two things can happen. If all controls contain data, it means a course has already been evaluated. If the same course needs to be re-evaluated at a later point in the year, the Add Data button should be used to add the data to a new row. If data is being added for the first time, the Update button should be used to post data to the same row.

My challenge is that the data that comes from the external source (first 4 columns, starting with Offering code, ending with Attendance) can be updated from time time - either with a new offering code and with an updated Attendance data. As a consequence, that data from the external source will need to be merged with the existing SP list.

I was thinking that the SP list will have to be downloaded to another place, then merged with the data from the external source via Power Query. The resulting output would then need to be uploaded to SP, replacing the existing list. Or is there a way to do this inline on SP?

Hope that makes sense.

[ppm1]

Not sure I totally understand your scenario. If your SharePoint list is updated (after Excel upload) via Power Apps, why isn't the SharePoint list up to date? How often do you bulk upload 3000 items and does it overwrite existing entries?

Are you hoping to write back to the SharePoint list from Power Query (not easy or recommended)? It would be better to use Power Query to combine your sources and create a paginated report subscription that runs after each refresh. You could then use that file in a flow that updates the items in the list.

Pat