Direct Query - Common User Editable?

[Hazenm]

Hello PBI Community! 

I'm wondering if anyone has come across this challenge and found a viable solution. 

So I have a few reports that require a fair amount of user enterable information, and I want that information to automatically update on my report. 
For example, I have a massive company wide weekly business report that refreshes every early Monday AM. All of the datasets are coming from automated sources, and so on Monday morning, all of this data is up to date. Except for one data source where my customer service team has to do some manual input. They have a spreadsheet on Sharepoint that they update monday am, and when ready, they give me the go ahead. I either have to refresh the entire report (which again, is massive, and takes ~hour) or I download the report, and just manually refresh the individual dataset, then re-upload to the server.
My solution was to create a dataverse table that I can modify with a spreadsheet from powerapps (not an actual powerapp, but just using the datatables within PowerApps), and connect it to the report via DirectQuery, so when the sheet is modified, it updates in realtime on the report. However, the problem is that in order to do so, I have to give the user some pretty high level permissions. They would need something that includes maker privileges, such as system customizer or environment maker. I have a few other reports where I want to do similar actions, so I can't just start handing out maker privelages around the company. 
Does anyone have an idea / has used before a different datasource that is DirectQuery that doesn't involve knowing how to code or requiring high level permissions? OR alternatively, have a different solution to this challenge? 

Thanks all!

[Hazenm]

You mean the "Power BI Service Live Connection" type? Yeah, unfortunately, this is disabled/not allowed on my report because "The Connect live option for this file is disabled because it already contains data from another data source. You cannot explore live data and connect to another type of data source in the same file." - I did try this one already. And if I'm understanding that message correctly, it's saying you can only use this option if it's the only type of datasource, correct?  

[Hazenm]

Hey Pete! 
Thanks for the idea! And I can see this working on some reports, but unfortunately, most or all of my other report sources are not Direct Query-able sources. My biggest sources are MySQL and OData. 
But that's a great idea.. if only we could use Power Automate to trigger individual table refreshes...! 

[BA_Pete]

Maybe you could set up your manual table as its own dataset and trigger the refresh of this dataset using Power Automate, then connect to this dataset as a new Live Connection source from your main report?

Not sure if LiveCon is compatible with other source types in one report though, would need testing.

Pete

[BA_Pete]

Hi @Hazenm ,

I think I would try and do it like this:

All tables Direct Query as far as possible.

Manual input table on SharePoint triggers a Power Automate flow when a file update is detected to push a dataset refresh to the report in the Service.

This automates the refresh trigger via Power Automate and, as all your other tables are Direct Query, the refresh time should be significantly reduced, as none of the other tables are actually loading any data to the model.

The only caveat would be for visuals where you display data from both the Direct Query and Manual tables in the same visual, as the join between these would have to be done Power BI-side, so may slow down end-user visualisation performance.

Pete