Theresa Eller Theresa Eller Business Systems Analyst at

  • Slides: 51
Download presentation
Theresa Eller | |

Theresa Eller | |

Theresa Eller • Business Systems Analyst at UT MD Anderson Cancer Center Business Systems

Theresa Eller • Business Systems Analyst at UT MD Anderson Cancer Center Business Systems Analyst at MD Anderson Cancer Center • Microsoft Office Specialist certified in Share. Point 2013 • President of Houston Share. Point User Group (HSPUG) • Frequent speaker at Share. Point events • Dallas, Paris, Houston, New York City, Baton Rouge, San Antonio, Raleigh • Mom to 2 furry, four-legged children (Shay & Cookie)

Agenda • Excel Tools • Power Query • Power View • Power. Pivot Gallery

Agenda • Excel Tools • Power Query • Power View • Power. Pivot Gallery • Power View Reports • Excel 2016 (time permitting)

Excel 2013 vs. Power BI Desktop Application Similarities Differences • Generate Power View reports,

Excel 2013 vs. Power BI Desktop Application Similarities Differences • Generate Power View reports, • Share Excel files on Share. Point charts, and maps sites via Excel Services • Numerous data sources, • Import Excel workbooks into including Share. Point, SQL Power BI Desktop Server, OData, and the web • Share Power BI files on Power BI • Store data on-premises or in the sites in Office 365 cloud (One Drive, Office 365) • Requires paid Power BI license

Requirements | Get Data | Edit Data | Load To…

Requirements | Get Data | Edit Data | Load To…

Power Query • An Excel add-in that enhances the self -service Business Intelligence experience

Power Query • An Excel add-in that enhances the self -service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration • A new add-in that provides a seamless experience for data discovery, data transformation and enrichment for Information Workers, BI professionals and other Excel users • Identify the data you care about from the sources you work with (e. g. relational databases, Excel, text and XML files, OData feeds, web pages, Hadoop HDFS, etc. ). • Combine data from multiple, disparate data sources and shape it in order to prepare the data for further analysis in tools like Excel and Power Pivot, or visualization in tools like Power View and Power Map.

Power Query Requirements • Operating System • Windows 7, 8, 8. 1 • Windows

Power Query Requirements • Operating System • Windows 7, 8, 8. 1 • Windows Server 2008 R 2 • Windows Server 2012 • Internet Explorer 9 (or higher) • Microsoft Office 2013: • Power Query Premium: All Power Query features available for: Professional Plus, Office 365 Pro. Plus or Excel 2013 Standalone • Microsoft Office 2013: • Power Query Public: Available for all other Office 2013 Desktop SKUs. Includes all Power Query features, except the following ones: Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, Share. Point Lists, Oracle, DB 2, My. SQL, Postgre. SQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP Business. Objects, Salesforce. • Microsoft Office 2010 Professional Plus with Software Assurance

Download & Enable Power Query Add-In • File > Options > Add-Ins > COM

Download & Enable Power Query Add-In • File > Options > Add-Ins > COM Add-Ins

Power Query Tab & Ribbon

Power Query Tab & Ribbon

Get External Data

Get External Data

Get Data from Share. Point List • Open a blank Excel document • Click

Get Data from Share. Point List • Open a blank Excel document • Click on the Power Query tab • Click From Other Sources • Click From Share. Point List

Share. Point Site URL and List Selection

Share. Point Site URL and List Selection

Load Options

Load Options

Load To Data Model • Handles hundreds of millions of rows of data

Load To Data Model • Handles hundreds of millions of rows of data

Workbook Queries

Workbook Queries

Edit / Launch Editor

Edit / Launch Editor

Query Editor Ribbon

Query Editor Ribbon

Remove Unnecessary Data

Remove Unnecessary Data

Flatten Columns

Flatten Columns

Close & Load • Updates the workbook queries with applied changes

Close & Load • Updates the workbook queries with applied changes

A Few Notes About Document Libraries • Libraries cannot be imported through the Share.

A Few Notes About Document Libraries • Libraries cannot be imported through the Share. Point list option • Might be able to import through Web option

Get Data from Microsoft SQL Database

Get Data from Microsoft SQL Database

Select Tables

Select Tables

Reports | Charts

Reports | Charts

Power View Requirements • Microsoft Excel 2013 • Office Professional Plus 2013 • Office

Power View Requirements • Microsoft Excel 2013 • Office Professional Plus 2013 • Office 365 Professional Plus • Standalone edition of Excel 2013 • Share. Point Server Enterprise Edition (2010 or 2013) • SQL Server 2012 SP 1 Reporting Services Add-In

Insert – Power View

Insert – Power View

Power View Sheet

Power View Sheet

Select Fields

Select Fields

Switch Visualization

Switch Visualization

Vertical Multiples

Vertical Multiples

Filtered by Year 2014

Filtered by Year 2014

Power Map • Bubble size represents Total Trip Cost • Bubble color represents Year

Power Map • Bubble size represents Total Trip Cost • Bubble color represents Year

Geocode Data Through Bing

Geocode Data Through Bing

Change Layout

Change Layout

Charts on Same Page are Connected • Slicer is a separate chart on the

Charts on Same Page are Connected • Slicer is a separate chart on the same page • Slicer icon is on Design tab Slicer

Power. Pivot • Powerful data analysis • Import millions of rows from multiple sources

Power. Pivot • Powerful data analysis • Import millions of rows from multiple sources • Excel COM add-in • Download for 2010 • Built-in for 2013 • Enable add-in if you don’t see the Power. Pivot tab

Power. Pivot Ribbon

Power. Pivot Ribbon

Get Data • Power Query • Power. Pivot

Get Data • Power Query • Power. Pivot

Insert – Pivot Table

Insert – Pivot Table

Power. Pivot Sheet

Power. Pivot Sheet

Select Fields

Select Fields

Slicer

Slicer

Data Refresh • Refresh – current connection • Refresh All – all connections

Data Refresh • Refresh – current connection • Refresh All – all connections

Data Refresh Summary ODC Power. Pivot Power Query Excel Client M M M Share.

Data Refresh Summary ODC Power. Pivot Power Query Excel Client M M M Share. Point/Excel Services/ Power. Pivot for Share. Point M A SQL Server Analysis Services Import A A Office 365 M M A Office 365 with Power BI A* A Power BI Dashboards A A M – Manual Refresh | A – Both Manual and Automatic Refresh | * only limited data sources Source: Using Excel with External Data – What’s the Right Tool? by John White

Excel 2016 |

Excel 2016 |

Improvements to Excel 2016 • New charting and analysis capabilities • Power Query is

Improvements to Excel 2016 • New charting and analysis capabilities • Power Query is fully integrated (no longer an add-in) • New Query button within Get & Transform group • Power Maps renamed to 3 -D Maps • Fully integrated on Insert tab • Power View tab missing • Must add it by going to Customize Ribbon • Search in the Pivot. Table field list Sources available at end of presentation

Questions? Theresa Eller | sharepointmadam@gmail. com | @Share. Point. Madam www. sharepointmadam. com |

Questions? Theresa Eller | [email protected] com | @Share. Point. Madam www. sharepointmadam. com | slideshare. net/sharepointmadam

Online Resources • Download SQL Server 2012 Express • https: //www. microsoft. com/en-us/download/details. aspx?

Online Resources • Download SQL Server 2012 Express • https: //www. microsoft. com/en-us/download/details. aspx? id=29062 • Download Adventure Works Database • http: //msftdbprodsamples. codeplex. com/ • Power BI Blog • https: //blogs. office. com/sku/power-bi/ • The White Pages • http: //whitepages. unlimitedviz. com/

Recommended Blog Posts • Helping business analysts take full advantage of Excel 2016 and

Recommended Blog Posts • Helping business analysts take full advantage of Excel 2016 and the new Power BI • What’s new for business analytics in Excel 2016 • Where Did Power View Go in Excel 2016?