Power Platform Community Forum Thread Details

Power Query for Excel uploads to SharePoint

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.

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.

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. This would result in List B.

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?

The process flow I would anticipate as follows, would only need the Power Query stage to be performed occasionally, batch wise, when an updated list from the external source (list A) is needed to replace/blend into SharePoint with the selections from the Power App (Input B)...

Hope that makes sense.

Regarding your comment about it being better to use Power Query to combine your sources and create a paginated report subscription that runs after each refresh, I'm not familiar with the term. Do you mean loading the outputs from the merger to a separate tab? If not. do you have a resource you can point me towards?

Thanks

Quick Links

Leaderboard > Power Apps - Power Query