cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dataverse Data in Excel

Hello everyone

I have deployed a Powerapp using Dataverse as data storage. To make the data easy accessible for the users to analyze and build their graphs, I plan to deliver an Excel file which has a connection to some Dataverse Tables. 

The following requirements are given: 

- The tables shown in Excel must be a query of multible tables in dataverse. May be similar to a FetchXML queries

- The data should be real-time, Power Query would be great.

 

I did not find any information or Youtube videos to this topic. Perhaps i have to use Dataflows for this requirement?

 

1 ACCEPTED SOLUTION

Accepted Solutions
dpoggemann
Super User
Super User

Hi @Anonymous ,

I work a lot with model apps (primarily) and you would normally create multiple views and even embed sub-grids in the forms of the parent (with the child records in the subgrid) vs. trying to create a view that has the multiple levels like you are suggesting.  I have gotten around this based on customer needs a lot in the past by doing the following, not specifically best normalization but meets my needs most of the time.  

 

In your case, if it makes sense, I would normally add a Lookup to Table A on table C as well and when I set the value of B on Table C I use a realtime workflow to set the value of A as well (since I can get to that value from table B).  With this you can create a view for Table C that contains values from Table A and B since they are both directly related through the lookup.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

View solution in original post

5 REPLIES 5
dpoggemann
Super User
Super User

Hi @Anonymous ,

 

My suggestion here is to setup a model-driven Power App for your users to utilize.  This will give them the ability to navigate the data, search, create their own personal views, filter data, and more through an application.  Users can also create their own Excel Templates (https://docs.microsoft.com/en-us/power-platform/admin/analyze-your-data-with-excel-templates), export data in views to Excel (static or dynamic / live worksheets) within the model-based Power App.

 

Overall I think would be a lot more straight-forward for the users to utilize and save you a lot of work trying to utilize Excel as your user experience for the users when the model-app can provides this platform in a controlled fashion.

 

Just my 2 cents.  I have never tried, or actually worked with anyone that has tried to do what you are doing so that is why I have suggested this alternate approach.

 

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
Anonymous
Not applicable

Hello @dpoggemann 

Thanks for your advice! I have less experience in model-driven apps than in canvas apps. 

It seems that I first have to configure a "view" for the desired table, which I will later add to my model-driven app. My only problem is that i have a complex structure of tables:

Desired table A has a lookup to table B.

Table B Has a lookup to table C.

Table C has a Field which I want to provide in the final view. 

 

For me it seems like the view editor cannot create queries over 2 tables. Is it by design impossible to work with such structures? For me it made sense to normalize some fields. I recognized this limitations in the canvas editor too. But in there, i was able to tweak this limitation by a lookup().

dpoggemann
Super User
Super User

Hi @Anonymous ,

I work a lot with model apps (primarily) and you would normally create multiple views and even embed sub-grids in the forms of the parent (with the child records in the subgrid) vs. trying to create a view that has the multiple levels like you are suggesting.  I have gotten around this based on customer needs a lot in the past by doing the following, not specifically best normalization but meets my needs most of the time.  

 

In your case, if it makes sense, I would normally add a Lookup to Table A on table C as well and when I set the value of B on Table C I use a realtime workflow to set the value of A as well (since I can get to that value from table B).  With this you can create a view for Table C that contains values from Table A and B since they are both directly related through the lookup.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
Anonymous
Not applicable

Hi Drew

thank you so much. I think i understand your idea. It might be a bit difficult to maintain the datafileds and keep them in-line.. but maybe I will apply this. A part of me hoped that there would be an easier way. It is a little disappointing to see that such an 'easy' data operation is not possible without changing the DataStructrure.

Anonymous
Not applicable

Ok this was a pretty good idea. Was very easy to implement by adding an invisible Datacard to all Forms which feed the "Table A". 

If you have a 2-grade-relationship in Power Platform, simply add a Lookup field to Table A which directs to Table C and all functions will be avilable without any Lookup() functions in the code.

I will now create a view to make excel download possible

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Users online (4,363)