Unanswered

Uploading Row from Excel table to Existing Dataverse Table and Automatically Map Columns.

(0) Share

Report

Posted on by ben_E_556

Hi, I have multiple Excel spreadsheets that have wide tables with one row and 100-150 columns. I also have a Dataverse table with all possible (150+) column headers and one existing row. I want to create a Power Automate flow that grabs the singular row from each Excel table and adds the row to my existing Dataverse table, automatically mapping column names that are the same across the sources. In cases where a particular column in the Dataverse table does not exist in the Excel table, the particular column should be blank. Any cases where the column header names exist in both Dataverse/the Excel table, the column should populate.

I know I can ingest a row using List Rows Present in an Excel Table -> Add a New Row and manually specify which columns map, but needing to do this over a hundred times for each Excel table seems unnecessary. I'm looking for guidance on how to automatically map columns between my Excel table and my Dataverse table and ingest the row from each Excel table.

Appreciate any help!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Shashank Bhide 946 Moderator on at

Like (0)

Report
Copy link

Link copied!

This is where elastic tables are very useful. However, for your static table scenario, the best way is using power automate IMO.

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/elastic-tables

Was this reply helpful? Yes No
sudipm Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@ben_E_556

If I understand correctly you are able to automatically map columns from an excel table to your table dynamically and are able to ingest it, however you want to automatically do this for all excels and tables contanied within the excel.

If the above is correct, something like below should be possible

Assuming you have your excels in either sharepoint or onedrive inside a folder

In power automate
List all files in the sharepoint/onedrive folder
Loop through them and if their extension is .xlsx (or whatever your search condition is)
Use the excel "GetTables" step to list all tables in excel
https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/#get-tables
Depending on the name of the table (or some other indicator like first column name etc)
Identify which table in dataverse it needs to map to
Then use your logic you have built to map things to process this table with dataverse.

Was this reply helpful? Yes No
ben_E_556 40 on at

Like (0)

Report
Copy link

Link copied!

I am not able to automatically map columns, I've clarified in my post

Was this reply helpful? Yes No
ben_E_556 40 on at

Like (0)

Report
Copy link

Link copied!

I am not able to automatically map columns, that primarily what I'm trying to figure out. I've clarified in my post. Thank you

Was this reply helpful? Yes No
sshoemaker512 8 on at

Like (0)

Report
Copy link

Link copied!

Did you get an answer to this?

Was this reply helpful? Yes No