cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryancascarano
Frequent Visitor

Workaround for utilizing Microsoft List as Direct Query data source and/or modifying data in report?

Problem: I have data contained in a SharePoint Online list that I want users to be able to modify by using an embedded Power App visualization. However, Lists aren't a Direct Query data source which renders the embedded Power App relatively useless after reconnecting it to the List. I've done this with good success with SQL data sources previously, but I don't have the ability to push this data back and forth to SQL.

 

Is there any (relatively) straight forward method to pipe data from Lists into a Direct Query data source? According to this: https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources, Power BI datasets are Direct Queryable, so I thought I would be able to connect the Power App to the Power BI dataset being produced, but it does not seem to be an option in Power Apps. I also tried creating a Dataverse table from the List but ran into other issues.

 

Otherwise, is there a method I may not be thinking of to do this? I basically have a single field that I want users to be able to modify in-report so that they can update projections that are being calculated from that field, but the Power App method described above is the only way I can come up with to do this. Of course they could go into the List, update the necessary fields, and manually refresh the report, but I was hoping for a cleaner, one-stop-shop type solution.

 

Thanks for any help.

1 REPLY 1
lbendlin
Super User
Super User

@ryancascarano 

"but I don't have the ability to push this data back and forth to SQL."

That's exactly what we have done in such a scenario. We have a SSIS package that frequently syncs the sharepoint list with a table in a SQL Server database.  That table is then used as the Direct Query source for the report.

 

Not ideal - you still have the UX lag - but it's at least a step closer to completing the UX for the data write back cycle.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors