Skip to main content Link Menu Expand (external link) Document Search Copy Copied
Tidy Data

Instructions were designed for windows, but include Mac images and instructions in situations where the navigation is different.

“UNDO” in Power Query

Before you begin, you should know that Power Query has no “undo” in the traditional sense. To remove a previous action on your data hover over the step in APPLIED STEPS in the Query Settings pane on the right. A red X should appear next to the step, click it to delete the step. If the Query Settings are not open:

  1. Click on the File tab in the top left-hand corner
  2. Select Options and Settings
  3. Select Options
  4. Under GLOBAL Select Power Query Editor
  5. Make sure Display the Query Settings pane is checked off
  6. Click Okay


Columns Headers are Values

Tidy Data Rule(s) Broken: Each variable forms a column


  1. Open a new project in Excel

  2. Open Power Query Editor from the Data tab in Excel

    [WINDOWS] a. Select “Get Data” b. Scroll down to “launch Power Query Editor…”

    [MAC] a. Click “Get data (Power Query)

See Navigation for Windows
See Navigation for Mac


IMPORT DATA:

  1. [WINDOWS]
    On the home tab, navigate to and select new data source, hover over File and select Excel Workbook. Notice that there are many different available data sources that Power Query can pull form.
    [MAC]
    Select Excel Workbook and click the browse button.

  2. Import the to-tidy.xlsx file from where you downloaded it.

  3. a. [WINDOWS only] In the Navigator window that pops up check off Select multiple items.
    b. then check off all the sheets from the to-tidy.xlsx workbook. Click OK or Load.

  4. From the menu on the left hand-side, select col-vals

Click for Navigation help [WINDOWS]
Click for Navigation help [MAC]


ADJUST HEADERS:

  1. Sometimes Power Query does not automatically detect that the table has headers. Click on the little table icon on the top left-hand corner of your data table. Select “Use First Row as Headers” from the dropdown menu.
Click for Navigation help

UNPIVOT COLUMNS:

The “messiness” of this table is because of the headers “issued” and “available” are actually values indicating the availability of a book. The values that they hold are the Book Ids, which should be in their own column with a descriptive heading.

  1. Select both the issued and available columns by holding CTRL if you’re on windows and command of you’re on a Mac and clicking on their headers.

  2. Once bother the columns are highlighted, right click on either header, select unpivot columns from the dropdown menu.

Click for Navigation help

GIVE NEW COLUMNS DESCRIPTIVE NAMES:

  1. Now the columns have the headings “Attribute” and “value” which are not very descriptive. Change the names by double clicking on the header. Use something descriptive that someone who has never seen your data before might understand, for example, “Availability” and “ID”
Click for Navigation help

NEXT STEP: Multiple Variables are Stored in One Column