Wrestling with Big Data? 6 winning moves in Excel 2016 or Power BI


Processing big data requires big worksheets. Unfortunately Excel 2016 is restricted to 1 million rows. To muscle past this limit combine Excel’s NEW QUERY command with its Data Model and your worksheet suddenly becomes unlimited.

  1. Choose DATA, NEW QUERY DataNewQuery then select a data source to import: File, Folder, Database, etc. Web can include Wikipedia pages, municipal data links and corporate SharePoint pages.
  2. In the Navigator [Click] EDIT, transform the data then close the Query Editor (CLOSE & LOAD TO) while making sure to [Click] ADD THIS DATA TO THE DATA MODEL.

Massive transaction tables typically contain many repeated codes (e.g., AR for Arizona). Lookup tables expand those codes to full descriptions. The combination of transaction and related lookup data is found in big data relational databases. Recreating these relationships within the Data Model makes importing easier and processing more efficient.

  1. Import transaction and associated lookup tables into the Data Model.
  2. Choose DATA, MANAGE DATA MODEL ManageDataModel then DIAGRAM VIEW DataModelDiag.
  3. Link transaction and lookup table by [Dragging] a transaction table column to its related lookup table counterpart. A line will form between them.
  4. Press <Ctrl-S> to save the changes.
  5. You can now create a PivotTable from lookup table descriptions rather transaction table codes.

Power View supplies the wow factor to any big data presentation. This is especially true of geo based data where you can spread columns of sales data across a 3D map and instantly locate points of interest.

  1. Install Power View.
  2. Import big data and associated geo data. Latitude/longitude combinations are best but separate columns of city, state and country data can be used if concatenated into a new calculated column using DAX formulas.
  3. Create a Power View sheet, [Drag] the data to appropriate sections of the Fields pane.
  4. [Click] MAP then ENABLE CONTENT and the data will be sent to Bing for geocoding.
  5. Format the map and create an interactive video tour.

Power BI

Power BI is built to handle big data. It offers easier and more comprehensive mechanisms for teasing meaningful and actionable visualizations from the piles of numbers. Its desktop application and Office 365 service work seamlessly to combine offline big data crunching with online team collaboration. Download the desktop version from https://powerbi.microsoft.com/en-us/desktop/. Sign up for the service via https://powerbi.microsoft.com/en-us/.

A recent update to the Power BI service allows you to query your big data using natural language questions, spoken or typed. Power BI searches through your datasets and reports then creates visualizations as answers. The highest-scoring results display first. To activate this feature:

  1. Enable the Cortana search engine in your Windows 10 version 1511 or higher.
  2. Use Windows 10 Account Settings to add your Power BI account then sign into Windows 10 with that account.
  3. Enable each dataset for Cortana. Results are improved if Cortana Answer pages are created at the same time.

Power BI Service has a unique feature called Quick Insights. It produces a series of insightful visualizations from your uploaded or imported data. It is most useful when you are stuck and need a new perspective on the data you are wrestling with.

  1. [Right Click] a dataset in the Power BI Service and choose QUICK INSIGHTS.
  2. Examine the resulting list of tiles and pin the interesting ones to a dashboard.

See our newly released guide Excel 2016: Business Analytics & Power BI for more detail.