Highline Excel 2016 Class 03-04 Introduction to Power Query

Introduction to Power Query

What does Query mean?

  • Query = Ask a Question.
  • Query in Data Analysis = Ask questions of Raw Data and Tables
  • In Excel we will ask Power Query to have the data imported, cleaned and transformed all with one tool!

Power Query = Get & Transform

  • New feature in Excel 2016 that allows you to import, clean and transform data.
  • Examples:
    • Clean Raw Data = Fix unusable raw data so that it can be used to perform data analysis.
      • Remove unwanted charters.
      • Add needed characters.
      • Split data apart into desired data.
      • Join data together to get desired data.
    • Transform Data Sets = Fix unusable data set so that it can be used to perform data analysis.
      • Filter, combine, merge, append or unpivot data sets.
      • Add, remove or filter columns in data sets.
    • Import Data = import data from external sources (single or multiple sources) into Excel or Power Pivot’s
  • History:
    • Before Excel 2016 it was called “Power Query”.
    • In Excel 2016 Microsoft changed the name from “Power Query” to “Get & Transform”.

Get & Transform group is in the Data Ribbon Tab:

  • New Query button = Open Power Query Editor
  • Show Queries button shows list of queries that you have made
  • From Table is button to click when you want to bring data from an Excel sheet into Power Query.
    • Data MUST be in an Excel Table before you can bring it into Power Query.
    • Why must it be in an Excel Table? It must be in an Excel Table so that if the data changes the Power Query output can be updated with the Refresh button.

What Power Query will do for us:

  • We can import “Source Data” from external sources or an Excel Table.
  • Clean and Transform the data.
  • Click the “Load To” button to load it back into new Excel Table or the Data Model (more on what the Data Model is in the next section).
  • The loaded data will sit in an Excel Table and can be refreshed by right-clicking and pointing to Refresh.

This this video we will see three examples of how to use Power Query:

  • Convert Improper Data Set into Proper Data Set:
    • Convert Excel Data to Excel Table in order to get it into Power Query
      • With single cell in Excel Table, click “From Table” button in the Get & Transform group in the Data Ribbon Tab.
    • In Power Query Editor:
      • Break Product, Date and Region Fields apart into 3 Separate Fields
        • We will use the “Split Column” button in the Transform group in the Power Query Home Ribbon Tab.
        • “Delimiter” means “Character that separates ‘Fields’ or ‘Bits of Data’”.
      • Be sure to Name your Query.
      • Be sure to check the Data Type for each Field.
      • Load Back to Excel
        • Click “Close & Load” in Close group in Power Query Home Ribbon Tab.
    • Make PivotTable.
    • Add new set of records:
      • You can paste whole new set of records below an Excel Table, and it will incorporate the new records into the data set.
    • Refresh Power Query output.
    • Refresh PivotTable.
  • Unpivot a Crosstabulated Table into a proper data set so we can perform sort, filter and PivotTable on Proper Data Set.
    • When you “Unpivot” a Crosstabulated table:
      • Row Headers becomes a single column
      • Column headers become a single column
      • Numbers on inside of Crosstabulated table become a single column.
    • Convert Crosstabulated table to an Excel Table.
    • Click “From Table” button in the Get & Transform group in the Data Ribbon Tab.
    • In Power Query Editor:
      • Select the first column, Right-click, then click on “Unpivot Other Columns”
      • Be sure to Name your Query.
      • Be sure to check the Data Type for each Field.
      • Load Back to Excel
      • Click “Close & Load” in Close group in Power Query Home Ribbon Tab.
    • Why do we want to Unpivot a Crosstabulated Table into a Proper Data Set?
      • Because once we have data in a Proper Data Set, we can use data analysis features like Sort, Filter, PivotTable.
  • Power Query and Power Pivot Data Model
    • Goal:
    • Import multiple text files that contain more than one million rows of data and combine (transform) them into a single table.
    • Create a relationship between Newly Combined Table and a Lookup Table.
    • Create a PivotTable from two tables.
    • Import text files using: “From File”, “From Folder” in the New Query drop-down in the Get & Transform group in the Data Ribbon Tab
    • The “From Folder” option in Power Query allows you to import all the files from a folder, and then combine them into one table.
      highline-excel-2016-class-03-04-01
    • After files are imported into Power Query Editor, right-click “Content” column and point to Remove Other Columns:
      highline-excel-2016-class-03-04-02
    • To expand Content, click Double Downward Pointing Arrows:
      highline-excel-2016-class-03-04-03
    • To remove Field Names from further down in the imported tables, Use the Filter at the top of the column with the fewest Unique Records to Filter out the Field Names:
      highline-excel-2016-class-03-04-04
    • Be sure to Name your Query.
    • Be sure to check the Data Type for each Field.
    • When you Load the data, Load it to “Only Create Connection” and be sure to check “Add this data to the Data Model”
      highline-excel-2016-class-03-04-05
    • Add Excel Table from an Excel sheet to the Data Model using the “Add to Data Model button in the Table group in the Power Pivot Ribbon Tab:
      highline-excel-2016-class-03-04-06>
    • Look at Data Model by clicking “Manage Data Model” in Data Tools group in Data Ribbon Tab:
      highline-excel-2016-class-03-04-07
    • To create a relationship between tables: In the Power Pivot Manage Data Model Editor, Click Diagram View button in View group, then drag Manager Field from Lookup Table to the Manager Field in the Transaction Table.
      highline-excel-2016-class-03-04-08
    • With a relationship between two tables, you can drag and drop Fields from both tables in the PivotTable Field list.
      highline-excel-2016-class-03-04-09




Be the first to comment

Leave a Reply

Your email address will not be published.


*