Reporting Sharepoint files and their status

[Anonymous]

Hi,

I have a sharepoint folder where a team are creating documents. Using custom columns and a Power Automate script these documents are being approved. Seperate to this I have an excel workbook which lists the documents which are expected to be created. The aim is for a Power BI report to report progress (how many documents have been approved, be person, by type, by area etc)

I've tried to acheive this a number of ways but each seems to have one barrier which prevents it quite working

1) Using Sharepoint.Files i'm able to list out the files in the sharePoint Folder. I don't seem to be able to access the custom columns so cannot tell when and who approved the file - giving me only half the information I need. It also seems to have an issue with UK to US dates flagging most as errors but i'm hoping thats a common and easy one to fix.

2) I've created a .iqy file and added this to the document list excel sheet. Each time that excel workbook is opened it triggers the query, pulls the information into a seperate tab and lookups compare the expected and actual document lists. I can then access this in Power BI and get the infromation I need. Trouble is the query is only activiated whern the Excel workbook is opened, which Power BI does not do. As a result my data is only up to date when someone has opened the workbook creating a manual step in an otherwise automated workflow. I've tried adding a refresh script in Excel Online triggered by a regular Power Automate task but it appears you can't have both a script and a query referenced in the Excel wookbook. If I add the script I have to go into O365 online edit mode which causes the the query connection to be lost as its unsupported, but when I add the query connection back in the script is then lost.

3) I've tried adding a websource and giving the URL of the .iqy file. Unchanged it gives me a file with 16 rows of trues and falses. Extracting row 3 which is a URL encounters an error - Details: "The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part)."

Each of these methods is so close  but not quite giving me what I need. Any ideas?

Thanks

Paul

[v-kkf-msft]

Hi @Anonymous ,

You could try to parse through a http call to:

api/web/lists/GetByTitle(‘<Title>’)/items(<ID>)?$select=OData__ModerationStatus

More information and some step-by-step procedures can be found here:

Microsoft Flow – Determine The Approval Status of a SharePoint List or Library Item 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, thanks for the reponse. I'm using Flow to create an approval workflow

What i'm trying to do is access that approval status in Power BI. I've tried multiple ways. I've numbered the ways the same as above but with screenshots this time

1) When I access the files using Sharepoint.Files I only get the standard columns, not the added approval columns 

2) I created a query iqy that excel can use to retrieve the information. It can be read and updates an excel file when its opened opening allowing me to read the information into Power BI through Excel. Trouble is this adds a manual step and relies upon someone remembering to open the file on a regular basis. I can't find a way of updating this excel sheet with the query results without manual intervention.

3) When I access the query iqy file in Power BI I get this

When I input line 3 

https://rskgad.sharepoint.com/sites/SouthernWaterProactiveMaintenance/_vti_bin/owssvr.dll?XMLDATA=1&...

as a new source I get this

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I create a custom column test custom in my SharePoint.

Then use SharePoint online List to connect to SharePoint, and the imported data contains custom columns.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.