Excel Business Intelligence Tools: Power Query, Pivot View & Map (Lab)

Presenter: Lisa Friedrichsen, Johnson County Community College In this hands on session we will use the new, integrated Business Intelligence (BI) tools in Excel. Power Query, Power Pivot, Power View, and Power Map to conduct effective data analysis and modeling. We will use Power Query to mine data from multiple sources such as Wikipedia, the S&P 500, and an Access relational database, Power Pivot to create a data model to summarize the data, Power View to create interactive reports and charts of the information, and Power Map to build 3-D data visualization maps.

1.0x

Excel Business Intelligence Tools: Power Query, Pivot View & Map (Lab)

Created 3 years ago

Duration 0:00:00
lesson view count 665
Presenter: Lisa Friedrichsen, Johnson County Community College In this hands on session we will use the new, integrated Business Intelligence (BI) tools in Excel. Power Query, Power Pivot, Power View, and Power Map to conduct effective data analysis and modeling. We will use Power Query to mine data from multiple sources such as Wikipedia, the S&P 500, and an Access relational database, Power Pivot to create a data model to summarize the data, Power View to create interactive reports and charts of the information, and Power Map to build 3-D data visualization maps.
Select the file type you wish to download
Slide Content
  1. Excel Business Intelligence Tools

    Slide 1 - Excel Business Intelligence Tools

    • Lisa Friedrichsen
    • Johnson County Community College
  2. Excel Business Intelligence Tools:    Power Query        Power Pivot            Power View                Power Map

    Slide 2 - Excel Business Intelligence Tools: Power Query Power Pivot Power View Power Map

    • Lisa Friedrichsen
    • Johnson County Community College
  3. Installing Power BI

    Slide 3 - Installing Power BI

    • https://support.office.com/en-in/article/Power-BI-for-Office-365-FAQ-37a30f7e-75be-442b-b5ef-3c2a0c324b6a?ui=en-US&rs=en-IN&ad=IN
    • How do I install Power Query, Power View, and other client tools?
    • You download Power Query separately.
    • http://www.microsoft.com/en-us/download/details.aspx?id=39379
    • Power Map, Power View, and PowerPivot are COM Add-Ins.
    • (File|Options|Add-Ins|COM Add-Ins|Go) (Component Object Model)
  4. Power Query

    Slide 4 - Power Query

    • “discover and connect to data from public and corporate data sources”
    • (Formerly Data Explorer)
    • Get Started with Power Query:
    • https://support.office.com/en-ie/article/Introduction-to-Microsoft-Power-Query-for-Excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605
    • Power Query tab
  5. Power Query – sources of data

    Slide 5 - Power Query – sources of data

    • Online Search or Web
    • From File…
    • Excel
    • CSV
    • XML
    • Text
    • Other Sources…
    • SharePoint
    • Exchange
    • Facebook
    • SAP
    • From Database…
    • SQL Server
    • Azure SQL
    • Access
    • Oracle
    • DB2
    • MySQL
    • PostgreSQL
    • Sybase
    • Teradata
  6. Power Pivot

    Slide 6 - Power Pivot

    • “create data models with relationships”
    • Get Started with Power Pivot:
    • https://support.office.com/en-ca/article/Get-started-with-Power-Pivot-in-Microsoft-Excel-2013-fdfcf944-7876-424a-8437-1a6c1043a80b
    • PowerPivot tab
  7. Power View

    Slide 8 - Power View

    • “create reports and analytical views through interactive charts and graphs”
    • (Formerly Project Crescent)
    • Get Started with Power View:
    • https://support.office.com/en-us/article/Power-View-Explore-visualize-and-present-your-data-98268d31-97e2-42aa-a52b-a68cf460472e?ui=en-US&rs=en-US&ad=US#__toc382586519
    • Insert tab | Power View icon
    • Also needs Silverlight
  8. Power Map

    Slide 10 - Power Map

    • “explore and navigate geospatial data on a 3D map experience”
    • (Formerly GeoFlo)
    • Get Started with Power Map:
    • https://support.office.com/en-us/article/Get-started-with-Power-Map-88a28df6-8258-40aa-b5cc-577873fb0f4a?ui=en-US&rs=en-001&ad=US#__exploring_sample_datasets
    • Insert tab | Map icon
  9. Power Business Intelligence Getting Started Guide

    Slide 14 - Power Business Intelligence Getting Started Guide

    • https://support.office.com/en-in/article/Power-BI-for-Office-365-Overview-and-Learning-02730e00-5c8c-4fe4-9d77-46b955b71467
    • 127 pages long
    • Power BI Learning Resources
    • http://social.technet.microsoft.com/wiki/contents/articles/18462.power-bi-learning-resources.aspx
  10. NYSE sample data:   http://go.microsoft.com/fwlink/?LinkID=389692

    Slide 15 - NYSE sample data: http://go.microsoft.com/fwlink/?LinkID=389692

    • NASDAQ sample data: http://go.microsoft.com/fwlink/?LinkId=389693
    • Sample Data
    • World Data Bank: http://databank.worldbank.org/data/home.aspx
    • Chicago Crime Data
    • Dallas Utilities Seasonal Electricity Consumption Simulation
    • Food Inspections in Seattle
    • Power Stations
    • Sample Data Sets (for Getting Started with Power Map)
  11. Microsoft acquired R in 2015

    Slide 16 - Microsoft acquired R in 2015

    • http://www.revolutionanalytics.com/
    • What is R?
    • R is world’s most widely used statistics programming language. It's the # 1 choice of data scientists and supported by a vibrant and talented community of contributors. R is taught in universities and deployed in mission critical business applications.
    • 2015 Cengage Learning Computing Conference
    • 16
  12. Let’s do it!

    Slide 17 - Let’s do it!