How to Create a Dataverse Table from Excel

Moving your data from an Excel spreadsheet into Microsoft Dataverse is one of the fastest ways to level up your business processes. It transforms your static data into a secure, scalable foundation you can use to build custom apps and automations. This guide will walk you through the entire process, step-by-step, from preparing your Excel file to creating your new Dataverse table.

Why It's Worth Moving Your Data from Excel to Dataverse

You might be wondering, "My data works just fine in Excel. Why go through the trouble?" Think of it like moving from a shoebox full of organized index cards to a powerful, digital CRM. While the shoebox works, the digital system unlocks possibilities you never had before. Moving your data from Excel to Dataverse offers several significant advantages:

• Centralized & Secure Data: Instead of having multiple versions of an Excel file floating around in emails and shared drives, your data lives in one central, secure location with controlled access.

• Build Custom Apps: Once your data is in Dataverse, you can use Power Apps to build mobile or desktop applications for your team to interact with that data - think custom inventory trackers, project management tools, or client management portals.

• Automate Workflows: You can connect your Dataverse tables to Power Automate to trigger actions automatically. For example, when a new record is added, you could automatically send an email notification or create a task in Microsoft To Do.

• Powerful Reporting: Connect your Dataverse data directly to Power BI to create dynamic, real-time dashboards and reports that provide much deeper insights than an Excel chart can.

• Data Integrity: Dataverse enforces data types and rules, meaning you can prevent users from entering text into a number field or leaving critical information blank. This keeps your data clean and reliable.

Step 1: Get Your Excel File Ready for Dataverse

A little prep work on your Excel file can save you a lot of headaches during the import process. Dataverse is more structured than a spreadsheet, so ensuring your file is clean and well-organized is the key to a successful transfer.

Format Your Data as an Excel Table

This is the most important step. Don't just import a range of cells, turn your data into a formal Excel Table first. This gives your data a defined structure that Dataverse can easily recognize. If you've never done this, it only takes a few seconds:

1. Click on any cell within your data range.

2. Go to the Home tab in Excel.

3. Click "Format as Table" and choose a style (the style doesn't matter).

4. Excel will confirm the data range. Make sure the box for "My table has headers" is checked, and click OK.

Your data will now be wrapped in a table with filter buttons on each header. This is exactly what you want.

Clean Up Your Column Headers

Your column headers in Excel will become the column display names in your Dataverse table. You'll have a chance to change them during the import, but it’s good practice to clean them up now.

• Make them unique: Ensure no two columns have the exact same header.

• Keep them simple: Avoid special characters like #, $, %, or &.

• No leading/trailing spaces: A space at the beginning or end of a header can cause a column to be misread.

Good Headers: FirstName, EmailAddress, OrderDate, Account_Manager

Bad Headers: Customer Full Name (Business), &Order#, Date

Check for Consistent Data Types

In Excel, you can get away with mixing data types in the same column. For instance, a "Quantity" column might have numbers (10, 25) but also text ("N/A", "Awaiting Stock"). Dataverse won't allow this. Each column must have one defined data type.

Go through each column and ensure the data is consistent:

• Number Columns: Should only contain numbers. Remove any text or symbols.

• Date Columns: Should only contain dates. Make sure every cell is formatted as a date.

• Text Columns: These are more forgiving, but it's still good practice to ensure consistency.

Pro tip: Use Excel's filtering feature to quickly spot odd or inconsistent values in each column.

Review and Handle Blanks

Decide what blank cells mean in your data. In Dataverse, a blank cell is treated as 'null' (no data). This is usually fine, but if a blank in your spreadsheet actually means zero (e.g., in a "Sales" column), you should use Find and Replace to fill those blanks with the number 0.

Once your file is prepped, save it and close it completely. The import process can fail if the file is open elsewhere.

Step 2: Creating the Dataverse Table from Your Excel File

With your prepped Excel file ready, it's time to log in to the Power Apps Maker Portal and start the creation process.

1. Navigate to the Tables Area

Go to https://make.powerapps.com/ and sign in. From the left-hand navigation pane, click on Tables (you might need to click Dataverse > Tables). This is where all your existing Dataverse tables live.

2. Start the Import

At the top of the screen, click the New table button and then select New table from data from the dropdown menu.

This will open a new workflow. You'll see a tile for Excel. Click on that, and it will prompt you to upload your file. Drag and drop your file or click "Browse" to locate it on your computer.

3. Map Your Columns and Configure Data Types

Once uploaded, Dataverse will read your file and show you a preview of your data. This is the most crucial configuration step, where you tell Dataverse exactly how to structure your new table.

For each column from your Excel file, you must review and configure its properties:

• Display name: This is a user-friendly name for your column. Dataverse will populate this from your Excel headers, but you can change it here. For instance, you could change "email_address" to "Email Address".

• Data type: Dataverse will do its best to guess the data type (Text, Whole Number, Date, etc.). You must review this for every single column. Getting this right is critical. Common data types to select include:

  • Text: For any text-based data (names, notes, categories).

  • Whole Number: For numbers without decimals (e.g., Quantity).

  • Decimal number: For numbers with decimals (e.g., Price).

  • Date and Time: For dates, times, or both.

  • Choice: Use this for columns with a predefined set of options (e.g., Status: New, In Progress, Completed). Dataverse will automatically create the choices based on the distinct values in your column.

  • Yes/No: For columns with values like TRUE/FALSE or Yes/No.

  • Currency: For monetary values.

• Primary name column: Every Dataverse table needs one "Primary Name Column." This is the main piece of text that identifies each row. You must choose one of your text columns to serve this purpose. For a customer list, it might be "Full Name." For a product list, it would be "Product Name." You cannot change this after the table is created, so choose carefully! Find the column you want to use and check the box that says "Make this my primary name column".

Take your time and go through each column one by one. Correct configuration here is the difference between a successful import and a frustrating failure.

4. Set Your Table Properties

After you've configured your columns, click the "Next" button in the bottom right. Here, you'll give your table its final properties:

• Table name: Provide a user-friendly name for your table (e.g., "Active Customer List"). This is what your users will see. It's best practice to use the singular form, like "Customer" instead of "Customers".

• Table schema name: This is the internal name Dataverse uses. It is automatically generated and usually doesn't need to be changed.

Beneath that, you can confirm all the column settings you just selected. Give them one final look-over.

5. Create Your Table

When you're happy with everything, click the Save button. Dataverse will now start two processes: first, it will create the table structure (the "schema"), and second, it will import all the rows from your Excel file into that new table. This can take a few minutes for larger files, so be patient.

Okay, the Table is Created. Now What?

Once the process is complete, your new table will appear in the table list. Click on it to explore. You can immediately see all the columns you configured and, more importantly, view the data itself.

By default, you'll see a view of your table's data, including all the rows imported from your spreadsheet. The number of records in this view should match the number of rows in your Excel file (minus the header row). Spot-check a few rows to ensure data appears in the correct columns and is formatted correctly.

Congratulations! You've successfully moved your data from a simple spreadsheet into a professional-grade database. From here, you can:

• Build a Power App to allow team members to view and edit the data from their phones.

• Create a Power Automate flow that sends a notification whenever a new customer is added.

• Connect the table to Power BI to create a live dashboard tracking customer activity.

Troubleshooting Common Import Issues

Sometimes things don't go perfectly. Here are a few common issues and how to fix them.

The Import Fails Entirely

If the process errors out before the table is created, the problem is almost always in your Excel file. Double-check that it is formatted as a proper table and that all your headers are clean and unique. The tiniest formatting issue can sometimes derail the upload.

The Table is Created, But Some Rows are Missing

This usually happens because of a data type mismatch. For example, if your "UnitPrice" column was set as a Currency data type, but a few rows contained text like "Call for price," Dataverse couldn't import those specific rows. When this happens, Dataverse will often give you a log of which rows failed and why.

The Data Looks Wrong in Dataverse

If dates look like strange numbers or numbers are sorting incorrectly, it's likely a column's data type was mapped incorrectly during the setup. For instance, a numeric postal code might have been mapped as a "Whole Number" when it should have been "Text" (since you don't do math with zip codes). Unfortunately, you can't change a column's data type after creation. The easiest fix is often to delete the newly created table and run the import process again, paying very close attention during the column mapping step.

Final Thoughts

Bringing your spreadsheets into Dataverse marks a significant shift from simple data storage to a dynamic data platform. By doing this, you're building a foundation that allows you to create sophisticated applications, automations, and analytics that were never possible with a standalone Excel file.

This process is perfect for centralizing operational data for your Power Apps, but most marketing and sales teams still find their most critical data scattered across other platforms. This is why we built Graphed to help. While Dataverse consolidates your internal app data, we connect to your external tools - Google Analytics, HubSpot, Salesforce, Shopify, and more - and bring all that performance data into one place. We make creating real-time dashboards as simple as asking a question in plain English, eliminating the manual grind of exporting those CSVs in the first place.