Create custom formula in Power Query

Power Query is a very powerful extension and requires more attention. So, for today, I have decided how to write and use a custom formula. It will be a simple example to show friendly name of the months based on month’s numbers.

Let’s imagine we have this data.
PowerQueryDummyData
And now we want to show friendly name of the month for each date in the “Start Date” column. Of course we can use some VBA formulas as well, but let’s use something more modern. Just go to “Power Query” ribbon to create a blank query, which will contain our custom formula.Actually the sequence is: Power Query -> Get External Data -> From Other Sources -> Blank Query. We name the query “ShowMonthName” and in Advanced Editor we enter the formula (to enter in the editor respect the sequence: “Home” ribbon -> “Query” tab -> “Advanced Editor” button).
Custom formula
What we actually did? We create a formula called “ShowMonthName” which can be used in Power Query connection. And I am going to show how to do this.

Place mouse inside used range containing dates and do the following: Power Query -> Excel Data -> From Table. Excel will select by default entire used range, so you probably do not need to adjust it and you can go to the next screen, which is our query editor window. Here you go to Add Column tab and click on Add Custom Column button from General tab. And for the next step you need to name your column and insert our formula in formula text area control. This is straight forward and there no other explanation needed.
Add custom column
If everything is correct, you will see the results immediately in the query editor window and, if there is no error, you can use the connection you have just created, which includes the custom formula.
Formula results
I really encourage you to start using Power Query. It will open a new world for you and you will love using it, because it is a step forward in reporting and business intelligence. Leave behind ugly VBA formulas and start looking for the future.

Advertisements

Combining Excel worksheets with Power Query

Microsoft released an extension for Excel called Power Query. It makes some tasks easier, like combining Excel worksheets in a single one. This is the subject I am going to talk about, but in the future I will cover more about Power Query.

Let’s assume we have a worksheet containing foods.

Food Worksheet
Food Worksheet

And we also have another worksheet containing electronics.
Electronics Worksheet
Electronics Worksheet

Both worksheets have the same columns and we want to combine them into a single worksheet to display all data. With Power Query this becomes an easy task. You just need to follow these steps.

Creating connection from tables

Click inside a worksheet used range and from Power Query ribbon choose to get data from table.

Get data from table
Get data from table

You are asked to choose the range you want to use for data. By default used range is selected.
Choose range
Choose range

Query editor window is opened. Just give a name to your query (I named it “Table_Food”) and choose “Close & Load To” option from “Close & Load” menu. From the new window choose “Only Create Connection” as we already have data into table.

Load To
Load To

Repeat this operation for the other worksheet, of course giving a different name for the connection (like “Table_Electronics”).

Create the connection to combine the data

To create a connection to show combined data, choose “Show Pane” from Power Query ribbon.

Show Pane
Show Pane

On the “Workbook Queries” pane, right click on any data connection and choose “Append”.
Choose Append Option
Choose Append Option

On the next screen choose source and destination for append query.
Choose source and destination
Choose source and destination

Give a name for your new query (like “Combined Data”) and again choose “Close & Load To” option from “Close & Load” menu, of course only creating connection.

Display data

Now, as you have created all connections, go to “Data” ribbon and choose to get external data from existing connection.

Get data from existing connections
Get data from existing connections

Choose your connection which displays data from both worksheets and click “Open” button.
Choose connection
Choose connection

On the next screen is really up to you what you choose. You can choose to display data into an existing worksheet or you can tell Excel to create a new for you to place the data. You can consider now the task is done. 🙂

There is an interesting part with Power Query. If, for example, you add new data into one of the worksheets you used as a source and refresh data connections, Power Query knows and put new data into combined connection.