New to this blog? Start the Intro to Power Query series here.
The Accounting Request
Recall we are producing the following report using the Bank account file below.
Access the Query
To transform the data, we now need to convert the Bank account listing from 7 columns in to 3 columns:- Bank,
- Bank Account Number, and
- Account Type.
Access the queries by selecting on the Data tab, Queries & Connections (Show Queries in some Excel versions). Right click on the "Bank_acct_LU" query and select "Edit" to open the Power Query (PQ) Editor. not needed.
Hold down the Ctrl key and select the following columns: "Bank", "Bank Account Number" and "Account Type".
On the Home tab, select the drop down menu on Remove Columns, then select "Remove Other Column".
The "Bank Account Number" column is in data type ABC 123, Any. PQ autoassigns the ABC 123 data type when it can't conclusively determine the data type.
Let's change the type of "Bank Account Number" column to data type "Text".
Today, the cash accountant keeps a sticky note on her desk notating which GL account number goes with which bank.
Let's add a GL account column by going to the "Add Column" tab and selecting "Conditional Column". Conditional columns are PQ's version of IF statements.
PQ uses plain English fields and drop down menus to create rules. Create the first rule using the parameters below.
Select "Add rule" to add in two additional rules for Chase and Bank of America. Then select "OK".
Change the data type of the new column to type "Text".
Let's do this again, but this time let's assign a dept for each bank account. Once again, the cash accountant keeps a sticky note to know how to code cash related to each account.
Go to the "Add Column" tab and select "Conditional Column". Create a rule as follows. Use "Add rule" to create the 2nd-4th rows.
Notice that the account numbers can be copied from the query and pasted into the Conditional Column box. Close the box, copy an account number, and then select the gear button for the Add Conditional Column step, and paste the account number in to the rule.
SOLUTION
Prior post: PowerBI and PowerPivot
In Power Query, there are two approaches for removing columns: selecting the columns to keep and Remove Other Columns or selecting the columns to remove and Remove Columns.
Since the Bank account listing is a manually maintained report produced by Treasury, it's possible that columns could be added or deleted. By choosing "Remove Other Columns", our analysis will be protected from future column additions or deletions.
Hold down the Ctrl key and select the following columns: "Bank", "Bank Account Number" and "Account Type".
Change Type
Let's change the type
On the column header, click on the "123". Select "ABC Text".
💥BEST PRACTICE TIP
Don't leave number and date columns as data type, ABC 123, Any. When queries are not working as expected, ABC 123 is a primary culprit as PQ functions frequently rely on a specific data type in order to operate correctly.💥
Let's start making connections between the GL balance data and the Bank account listing.
💥BEST PRACTICE TIP
Don't leave number and date columns as data type, ABC 123, Any. When queries are not working as expected, ABC 123 is a primary culprit as PQ functions frequently rely on a specific data type in order to operate correctly.💥
Add Conditional Column
Let's add a GL account column by going to the "Add Column" tab and selecting "Conditional Column". Conditional columns are PQ's version of IF statements.
PQ uses plain English fields and drop down menus to create rules. Create the first rule using the parameters below.
Select "Add rule" to add in two additional rules for Chase and Bank of America. Then select "OK".
Go to the "Add Column" tab and select "Conditional Column". Create a rule as follows. Use "Add rule" to create the 2nd-4th rows.
Notice that the account numbers can be copied from the query and pasted into the Conditional Column box. Close the box, copy an account number, and then select the gear button for the Add Conditional Column step, and paste the account number in to the rule.
Also note, department 078 has a single quote ' before the 0. This indicates to PQ that this is text and not a number and will retain the leading zero.
When finished with the rules, change the data type of the Dept column to "Text".
We now have a look up table that provides the GL account number and dept coding for each bank account. Hard coded knowledge which doesn't disappear if the sticky note gets lost, or the accountant leaves their current role.
Close & Load.
In this lesson, you learned how to:
We now have a look up table that provides the GL account number and dept coding for each bank account. Hard coded knowledge which doesn't disappear if the sticky note gets lost, or the accountant leaves their current role.
Close & Load.
In this lesson, you learned how to:
- Future proof using Remove Other Columns,
- Understand the ABC123, Any data type,
- Create conditional columns, and,
- Copy data from queries and paste in to rule prompts.
SOLUTION
Prior post: PowerBI and PowerPivot
Next post: Automation
💬 Can you see the potential to use PQ in your own work? I'd love to hear about examples in your work that might benefit from PQ use. Let me know in the comments.
💬 Can you see the potential to use PQ in your own work? I'd love to hear about examples in your work that might benefit from PQ use. Let me know in the comments.
No comments:
Post a Comment