20779 Analyzing Data with Excel - Key Points

Data Analysis in Excel

• When connecting to SQL Server in the Excel, if you clear the Encrypt connection check box in the data connection wizard, data will be transferred from SQL Server to Excel in plain text. A malicious user might be able to intercept and examine unencrypted data. Unless you are sure that no data in the database is sensitive or confidential, always encrypt the connection.

Importing Data from Files

• You can choose to load data from the preview window when you import data. The same two options, Load and Load To, are available at the preview. Unless you are certain that the data is in the format you require, use Query Editor to check and modify data as needed.

Importing Data from Excel Reports

• Using the Advanced Editor in the Query Editor has several advantages. For example, you can perform tasks not supported by the user interface, such as creating filters with more than two clauses, or performing unpivot operations and renaming the result columns at the same time (using the user interface, you must rename the columns as a separate step). You can also change the source if you need to perform the same transformations against a different report.

Creating and Formatting Measures

• Before you use time intelligence functions in your calculated columns and measures, you must have a date table in your Data Model and have created appropriate relationships between that date table and any date columns in your Data Model.

Visualizing Data in Excel

• If you are constructing a data model to build visualizations, consider hiding data that is not relevant to the visualizations. Examples include primary and foreign key identity fields (for example, a product ID or a sales order ID). These fields are used to relate data between tables together, but the values that they contain are not meaningful on any report or chart. Similarly, consider creating measures to abstract data and make it easier to consume in a visualization. For example, a sales table will have a record for every sale, but you could also include a measure that calculates the year-to-date sales for given criteria.
• Excel provides a number of new chart types that you can use for visualizing data. These chart types include Treemap, Sunburst, and Histogram charts. However, while you can use these chart types to present information retrieved from a data cube, you can use them with PivotTables. If you wish to use them with the data held in an Excel Data Model, you must use cube functions to extract the required information. 
• You should be aware that when you convert from a PivotTable to formulas you lose some functionality. For example, the ability to perform label and value filtering is no longer available. However, any slicers that you defined will continue to operate; the cube formulas are written to reference them.

Using Excel with Power BI 

• A personal OneDrive service is also cloud-based, but Power BI does not support personal Microsoft accounts. Therefore, when you first connect to an Excel spreadsheet in a personal OneDrive account, you must sign in with your personal account. If you select the Keep me signed in option, Power BI can continue to synchronize automatically with the Excel workbook as it does with OneDrive for Business.
• You should ensure that any ranges of data in the Excel workbook are converted to named tables or added to the data model before you use the workbook in Power BI. Data in ranges is not fully supported in Power BI.