Data source files from a Microsoft Excel spreadsheet file must have the XLSX extension (signifying a Microsoft Office Open XML Workbook file) and be a maximum size of 50 MB.
Before you can upload a Microsoft Excel file as a data source, it must be structured in a data oriented way. Note the following rules for Excel tables:
Tables need to start in Row 1 and Column 1 of the Excel file.
Tables need to have a regular layout. There should be no gaps and inline headings. An example of an inline heading is a heading that repeats itself on every page of a printed report.
Row 1 must contain the table’s column names. For example, Customer Given Name, Customer Surname, Year, Product Name, Amount Purchased, and so on. In this example:
Column 1 has customer given names
Column 2 has customer surnames
Column 3 has year values
Column 4 has product names
Column 5 has the amount that each customer purchased for the named product
The names in Row 1 must be unique. Note that if there are two columns that hold year values, you must add a second word to one or both of the column names to make them unique. For example, if you have two columns named Year Lease, then you can rename the columns to Year Lease Starts and Year Lease Expires.
Rows 2 onward are the data for the table, and they cannot contain column names.
Data in a column must be of the same kind because it is often processed together. For example, Amount Purchased should only have numbers (and possibly nulls) so it can be summed or averaged. Given Name and Surname should be text as they may need to be concatenated, and dates may need to be split into their months, quarters, or years.
Data must be at the same grain. A table should not contain both aggregations and details for those aggregations. For example, suppose you have a sales table at the grain of Customer, Product, and Year, and contains the sum of Amount Purchased for each Product by each Customer by Year. In this case, you would not include Invoice level details or a Daily Summary in the same table, as the sum of Amount Purchased would not be calculated correctly. If you need to analyze at invoice level, day level, and month level, then you can do either of the following:
Have a table of invoice details: Invoice number, invoice date, Customer, product and amount. These can be rolled up to day or month or quarter.
Have multiple tables, one at each grain (invoice, day, month, quarter, and year).