How to Create an Excel Data Source

Overview

Cosmos allows you to use an Excel file as a data source in your Data Model, letting you blend spreadsheet data with your other reporting data just as you would with a standard Business Central data source. This article covers how to create and prepare your Excel file, check it in, and add it as a data source in the Data Model.

Preparing Your Excel File

To create your Excel data file, you will navigate to Reports in the left navigation panel, click + New, and create a new Excel file. Give the file a meaningful name—for example, Excel Import—and enter your data across one or more sheets as needed.

To add the required data type context, you will insert a new row above your data rows to specify the data type for each column. Cosmos uses this row to determine field types when the file is ingested into the Data Model. 

Data type values can be:

• Text
• Date
• Decimal
• Number
• Boolean

To save your work and make the file available to the Data Model, you will close the Excel editor and then click Check Out followed by Check In on the file's detail page. The Check In Report panel will appear on the right—click Check In to confirm. The file status will update to Checked In.

Adding the Excel File as a Data Source

To open the Data Model for editing, you will click Data Model in the left navigation panel under Data Modeling, then click Check Out to enable editing, and then click Edit.

To add a new data source, you will click + Add Data Source at the bottom of the Data Sources panel. The Add Data Source panel will open on the right. Set the Type field to Excel, enter a descriptive name in the Name field (for example, Sales Data Import), and select your Excel file from the File dropdown. Once complete, click Save.

To verify that your data is valid, you will click Validate in the Add Data Source panel after saving. Cosmos will check the file for errors or missing information and confirm that everything is in order before you proceed.

To add the Excel sheet's fields to the Data Model, you will click the + icon next to Source Tables under your new data source. The Add Tables panel will open, displaying the available sheets and fields from your Excel file—including their detected data types. Select the fields you want to include and click Add selected table. From this point, you can work with these fields—adding them, applying transformations, lookups, and fix values—exactly as you would with any other data source in the Data Model.