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

[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!

[shashankbhide]

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

[sudipm]

@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.

[ben_E_556]

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

[ben_E_556]

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

[sshoemaker512]

Did you get an answer to this?