Power Query sum two columns from different tables

[tiredofit]

I have loaded two sales data tables into the PQ editor.

Each table only contains a single column of data.

One is called April and the other May.

All I want is to create a new table containing a single column called Total, which sums each row within the two tables.

How can this be achieved?

Thanks

 

[tiredofit]

I really don't get PQ. It's taken me half a day, with your help and I still can't add two columns of data.

In my latest attempt, Sheet1 contains April's data AND an Index column.

Sheet2 only contains May's column of data.

I entered your formula into the Advanced Editor.

No errors were shown but I could only Close and Load. The option to Close and Load To was not available.

I chose Close and Load.

The PQ editor closed but I didn't see the Summed column in Excel.

I'm sticking to VBA from now on, even if it means I lose jobs!

 

[tiredofit]

Here is file example
Example

Thanks for your help.

I finally got it to work.

These were my steps:

1. Added the Index column manually to both tables.
2. Merged the two tables to Table2, choosing Index as the common field.
3. Select only the Data column.
4. Add a new column to Table2 and create the Sum function.
5. Close And Load To Excel.

Seems a lot of work just to add two columns of data.

 

[smozgur]

Seems a lot of work just to add two columns of data.

If there were a shared ID column, it would have been easier.

It is only four lines of code in M two of them are just for data import, but writing code is not as convenient as using the interface. I'm posting it as a reference for using the List.Zip function and the #table constructor for a case like this.

let
    // April column from Table2 as list
    List1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][April],
    // May column from Table2 as list
    List2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content][May],
    // Merge this two lists and create a table by using #table constructor
    MergedTable = #table({"April", "May"}, List.Zip({List1, List2})),
    // Add total
    Total = Table.AddColumn(MergedTable, "SUM", each ([April] + [May]))
in
    Total

That being said, for this particular process, I agree that VBA is a much better option unless you need to perform further transformations.

Edit: Changed the "Table" identifier I used as "MergedTable" to avoid confusion with the function names,

 

[tiredofit]

If there were a shared ID column, it would have been easier.

It is only four lines of code in M two of them are just for data import, but writing code is not as convenient as using the interface. I'm posting it as a reference for using the List.Zip function and the #table constructor for a case like this.

let
    // April column from Table2 as list
    List1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][April],
    // May column from Table2 as list
    List2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content][May],
    // Merge this two lists and create a table by using #table constructor
    Table = #table({"April", "May"}, List.Zip({List1, List2})),
    // Add total
    Total = Table.AddColumn(Table, "SUM", each ([April] + [May]))
in
    Total

That being said, for this particular process, I agree that VBA is a much better option unless you need to perform further transformations.

Thanks.

I've been using VBA for many years and have yet to come across a situation where VBA fails and Power Query succeeds, although as can be seen, my PQ skills are next to nothing!

The reason I'm investigating the PQ route is I have a workbook contain many formulae and is taking a long time to refresh.

I was experimenting using PQ to build the links, then refreshing but it seems just to sum two columns requires a lot of effort, as you've pointed out when there is no common field between the tables.

Looking at M, it seems just as complicated, if not more, than VBA.

 

[smozgur]

Looking at M, it seems just as complicated, if not more, than VBA.

You are right, it might seem complicated at first, especially for a VBA programmer, because it is a functional language, and you need to accept that it is not VBA. However, having experience in another programming language really speeds up the learning process.

For data transformation, Power Query (PQ) is amazing, and with M, you feel like the sky is the limit.

 

[tiredofit]

You are right, it might seem complicated at first, especially for a VBA programmer, because it is a functional language, and you need to accept that it is not VBA. However, having experience in another programming language really speeds up the learning process.

For data transformation, Power Query (PQ) is amazing, and with M, you feel like the sky is the limit.

I'm sure you're right but since I have written lots of VBA to do data transformation (and succeeded), it seems pointless to reinvent the wheel by venturing down the PQ path.

If only there was something only PQ could do (and VBA can't) would it interest me to explore.

DAX seems even more of a headache and don't get me started on Power BI!

 

[smozgur]

I'm sure you're right but since I have written lots of VBA to do data transformation (and succeeded), it seems pointless to reinvent the wheel by venturing down the PQ path.

If only there was something only PQ could do (and VBA can't) would it interest me to explore.

DAX seems even more of a headache and don't get me started on Power BI!

I've been programming in VB/A for more than 25 years, and I had the same thoughts when I was first introduced to Power Query. There is no doubt that VBA will always be my only choice for developing Office applications. However, there are certainly many tasks that can be done much easier in Power Query. Besides, Power Query is mainly designed for data transformation. Let me mention some of the advantages to encourage you to continue exploring Power Query (and you don't have to start with M right away; you will know when you need it).

Using Power Query is highly recommended for handling large data sets. I can't imagine attempting to work with thousands of rows and columns using VBA, especially when compared to the lightning-fast performance of Power Query.

You can easily import data from various sources, such as CSV files, JSON data, databases, remote data, web services, and more. While it is possible to accomplish this in VBA (even though there is still no native JSON adapter!), Power Query provides dedicated functions for accessing all kinds of data sources. In VBA, you would have to write lots of lines of code to achieve the same functionality while you would only need a single function to import the same data.

Debugging is somewhat easier in Power Query since you can examine individual steps and the output of each step.

Not to mention the PQ interface and powerful M functions that can be used for all sorts of transformations.

As I mentioned, it is important to accept that Power Query (and M) is not VBA. For example, you might feel frustrated because creating a simple loop is not as straightforward. However, this is because you often won't need to create traditional loops in Power Query since many transformation functions already include looping functionality. Of course, you can still create loops in M, even rarely.

it seems pointless to reinvent the wheel by venturing down the PQ path.

Believe me, they are surely different things. I am still developing in VBA. In fact, my recent add-in, which fills in some important but missing Power Query features on Mac OS, was written in VBA!

In the end, investing time to learn a new programming language is, of course, a decision to be made. I understand that being skilled in VBA programming might stop you from delving into Power Query. However, Power Query is a powerful environment, especially if you often need data transformation. It is worthwhile to spend some time exploring it.

 

[tiredofit]

I've been programming in VB/A for more than 25 years, and I had the same thoughts when I was first introduced to Power Query. There is no doubt that VBA will always be my only choice for developing Office applications. However, there are certainly many tasks that can be done much easier in Power Query. Besides, Power Query is mainly designed for data transformation. Let me mention some of the advantages to encourage you to continue exploring Power Query (and you don't have to start with M right away; you will know when you need it).

Using Power Query is highly recommended for handling large data sets. I can't imagine attempting to work with thousands of rows and columns using VBA, especially when compared to the lightning-fast performance of Power Query.

You can easily import data from various sources, such as CSV files, JSON data, databases, remote data, web services, and more. While it is possible to accomplish this in VBA (even though there is still no native JSON adapter!), Power Query provides dedicated functions for accessing all kinds of data sources. In VBA, you would have to write lots of lines of code to achieve the same functionality while you would only need a single function to import the same data.

Debugging is somewhat easier in Power Query since you can examine individual steps and the output of each step.

Not to mention the PQ interface and powerful M functions that can be used for all sorts of transformations.

As I mentioned, it is important to accept that Power Query (and M) is not VBA. For example, you might feel frustrated because creating a simple loop is not as straightforward. However, this is because you often won't need to create traditional loops in Power Query since many transformation functions already include looping functionality. Of course, you can still create loops in M, even rarely.


Believe me, they are surely different things. I am still developing in VBA. In fact, my recent add-in, which fills in some important but missing Power Query features on Mac OS, was written in VBA!

In the end, investing time to learn a new programming language is, of course, a decision to be made. I understand that being skilled in VBA programming might stop you from delving into Power Query. However, Power Query is a powerful environment, especially if you often need data transformation. It is worthwhile to spend some time exploring it.

I understand PQ had it's merits, otherwise it wouldn't be so popular.

I'm digressing now.

I'm always interested to hear the views from seasoned VBA developers like yourself.

We know VBA is not totally OO because it lacks inheritence but nevertheless, do you write VBA in an OO way or strictly procedural?

My view is that a program contain classes doesn't necessarily make it OO but an OO program will contain classes.

 

[Kerryx]

Thanks for your help.

I finally got it to work.

These were my steps:

1. Added the Index column manually to both tables.
2. Merged the two tables to Table2, choosing Index as the common field.
3. Select only the Data column.
4. Add a new column to Table2 and create the Sum function.
5. Close And Load To Excel.

Seems a lot of work just to add two columns of data.

Perfect , glad to see you picked up a new skill, yes for someone used to VBA programming it seems a lot but for people like me very limited VBA skills and working with big data sets PQ is great, can do almost all my work in it and save it to be reused over and over again when needed