Getting data in to Power Query is simple.
Download exercise files
Follow along by either downloading our files below, or working with one of your own Excel files that has a single worksheet with a simple data table.Select the "Download" icon below to download each of the two exercise files. Then save & close.
Bank account listing
⇧
GL Balances
⇧
Choose data
In Excel, open a New workbook.Office 365 and Excel 2019 users
In the ribbon, go to the Data tab, Get & Transform Data section, and select Get Data, From File, From Workbook.
Excel 2016 users
In the ribbon, go to the Data tab, Get & Transform section, and select New Query, From File, From Workbook.
Excel 2010 and Excel 2013 users
In the Power Query tab, select From File, From Excel.
All users
Browse to the previously saved exercise file "Bank account listing".
Select “Import”.
On the pop-up Navigator screen, select “Sheet1”, which will provide a preview of the Bank account listing file. Select "Transform Data" for newer Excel versions, or "Edit" for Excel 2010-2013.
Congratulations! You have now entered the “rabbit hole”: a hidden world full of amazing tricks sure to stir the imagination. Microsoft calls this the Power Query Editor. On the right hand side, the query name defaulted to the worksheet name, Sheet1. Let's rename to "Bank_acct_LU" by highlighting the name "Sheet1" and typing over it.
💥 BEST PRACTICE TIP
Carefully name your query when loading data.
Using No Spaces and labeling the query as either a Data query or a Lookup (LU) query can be helpful when building your data model and formulas in Power Pivot. 💥
On the Home tab, select “Close & Load To”.
On the Import Data screen, select "Table", "New worksheet", and the box for "Add this data to the Data Model". Select "Ok".
Recap
The steps above imported the data from the "Bank account listing" file in to a new workbook, creating a query that is saved within the new Excel workbook in an Excel Table format. Since the query has not been modified yet, the output looks very similar to the source file, except for formatting.
Query Table
Bank account listing
Take note of the new Queries & Connections pane on the right side of the worksheet listing the "Bank_acct_LU" query. If it's missing, on the ribbon, go to the Data tab, Queries & Connections button to reopen; in some versions of Excel, Queries & Connections is Show Queries.
Query Table
Bank account listing
Repeat
Repeat the steps above to create a GL balance query named "GL_bal_Data" using the previously downloaded file "GL Balances".
The source files are now loaded, completing Step 1 of The CPA Revolution, Choose.
The source files are now loaded, completing Step 1 of The CPA Revolution, Choose.
Next time the accountant receives a new file, she can save the new file in the same location with the same name; then, right click on the query name and "Refresh". The load is now an automated process, kicked off by Refresh.
In this lesson, you learned how to:
In this lesson, you learned how to:
- Load data in to the Power Query Editor.
No comments:
Post a Comment