Importing data from Excel into the Common Data Services using Power Query

Alfwyn Jordan, 17 April 2019

While working with CDS I was attempting to import data to a newly created entity (“prospects”) from an Excel workbook. I’d tried using the “Get data from Excel” functionality only to be greeted with the following message: “Your import is done. 0 row(s) successfully imported to cr3e2_prospects entities.”

A Power Query lookup is the best workaround. Initially I had to choose the data source for this query, and of course chose Excel.

Then I selected the file from my OneDrive.

After reviewing the table to ensure that it was the correct table, I moved onto the next screen where things didn’t quite look right. The column headers were in the first row.

To fix this I clicked on Transform table and promoted the first row to column headers.

Which resulted in the following. Much better!

On to the next stage where we load to an existing entity. Remembering to search for the entity by its schema name, in this ‘case cr3e2_Prospects’. And then map the source columns on to the destination fields.

Then after selecting the refresh settings, in this case manual, the file was loaded on to the CDS. I clicked through to the Data tab of my entity and checked to ensure all the data had been added, and with no surprises there it was.

And that is how I imported the data from an excel file to an entity in PowerApps.