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

Data import/export

Hi all,

 

I have a Power BI dashboard which I manually enter data into. Work would like to remove me as a single point of failure in using the dashboard by making it easy for anyone to import/export the data in the dataverse.

 

My thought are to have an excel spreadsheet which uses data validation to ensure the correct data is being entered. The values come from their own tables, and are stored in the data table as codes. For example, they enter a location, and that comes from a list of locations in the location table. Then they enter a department, and that comes from a list of departments in the department table. 

 

So because the table they need to update is full of codes, not the values, the import/export will need to do some look ups to transform the data. 

 

How would you do it? Create an app with import/export buttons? Use a flow from excel? Also is there a way to use a table in the dataverse to create the dropdowns in excel for the validation? I don't want to manually update these separately if the lists get changed, but I also don't want end users to be able to change these lists themselves.

 

What are your thoughts?

 

Ta 

 

 

 

9 REPLIES 9
ChrisPiasecki
Super User
Super User

Hi @KathrynMarie,

 

Have you looked at the Edit in Excel feature? It let's you export your data to Excel and uses an Add-In to connect directly to your Dataverse table and add/modify data. It will also have choice or lookup columns available so you can ensure the right data types are being entered, and will validate your data when publishing back to Dataverse.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Yes that's why I use to add the data. A couple of issues with using this for an end user. One is that I, in no way, want them to be able to edit the lookup lists. Also, the data they add needs to be in the format where the codes are used, not the list item, so the data needs to be transformed in some way. It would be too difficult for end users to deal with codes.

Hi @KathrynMarie,

 

When you select a Lookup column, it brings up the list of records in the Add In Pane, and shows both the ID (code) and primary Name (value). Therefore you should be able to easily choose the value and not have to know the code necessarily. 

 

With regards to protecting the Lookup lists, you can use Security Roles to prevent users from creating/editing/deleting those records and instead just provide Read/Append To privileges to the 'Lookup' Tables.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Do you mean from within a power app? People won't be adding info one at a time, it will need to be a bulk upload, I I think I'd need to use a button that transforms the data. Within excel I would need data validation so only valid information is added.

ChrisPiasecki
Super User
Super User

Hi @KathrynMarie,

 

You may want to look at the classic Data Import Wizard then to bring in your Excel/CSV files. You can configure a Data Mapping such that your lookups are searching by the "code" that you are using. You can save that Data Map so that you don't have to do this manual mapping exercise every time you import a file, but instead can just choose the Data Map upon import. As long as your codes are unique values in Dataverse, it will be able to find the correct row to transform.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

EricRegnier
Super User
Super User

Hi @KathrynMarie,

I also recommend the Excel add-in @ChrisPiasecki suggested. Note that Dataverse will perform validation on the back-end so if ever the user updated a lookup or messed up the Excel, the updates won't work and errors will be throw within the Excel file. I wouldn't recommend CSV and Excel import, it's more complicated and subject to error, especially if your users find the Excel add-in complicated.

The other option is why not create a very simple model-driven app to update the data in bulk? You can enable the editable grid and/or can leverage the open in Excel online function which is simple to use.

Hope this helps!

I think I'll need to create the app. I have tried the data mapping and am having no luck. I can import data, but can't figure out how to map it to the lookup tables. I will note, the lookup tables are just other tables, I am not using the lookup lists.

Fubar
Solution Sage
Solution Sage

Re: mapping lookups Last time I looked at it it was still easier to do from the Classic Interface ("Switch to Classic" when in make.powerapps.com).

Your main record needs to have Lookup fields to the other entities/tables (the ones that contain the set of valid codes and values)

 

You can map to fields other than the name as per the following link (it is an old link but it is still valid) - but the value Column that you map to must be unique (if your source file has a value that appears 2 or more times in the Lookup table you will get an error as the import does not know which record to use):  https://powerobjects.com/crm-101/importing-lookup-values-using-crm-2011-data-import-wizard/

KathrynMarie
Frequent Visitor

I've been around and around with this one, and I still can't figure out how to do it. The two main issues seem to be the mapping (converting the text into a code on import), and getting the table of data from excel without giving the end user access to the actual excel sheet where they could potentionally delete/edit previous data.

 

I have tried setting up a sharepoint list and can upload the file there, but I can't get the content of the excel table out, let alone convert it before creating new records in the dataverse.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,427)