Another episode of the Dueling Excel podcast. This time, we tackle Siva's problem of how to add 500 to a cell that contains many numbers: 123,234,345,456. Last week, in Duel 192, Bill used VBA and Mike used LET. Today, we both use Power Query, but in completely different ways. Bill seeks out the Power Query equivalent of the DAX CONCATENATEX function and finds a formula bar hack with Text.Combine. Mike seeks a single-formula solution in Power Query but runs into a problem using "each" with two iterators.
Table of Contents
(0:00) Introduction of the problem
(0:37) Bill's Power Query solution
(2:31) Concatenating text in Power Query with Text.Combine
(3:39) Mike's Solution
(6:09) Mike explains "each" keyword
(6:55) Difficulty due to two iterators
(8:45) Mike uses an explicit variable
(4:30) Iterating over each row in Power Query
(11:19) Bill story about ModelOff game
Table of Contents
(0:00) Introduction of the problem
(0:37) Bill's Power Query solution
(2:31) Concatenating text in Power Query with Text.Combine
(3:39) Mike's Solution
(6:09) Mike explains "each" keyword
(6:55) Difficulty due to two iterators
(8:45) Mike uses an explicit variable
(4:30) Iterating over each row in Power Query
(11:19) Bill story about ModelOff game
Transcript of the video:
Hey welcome back. It is time for another Dueling Excel podcast.
I am Bill Jelen from MrExcel. We'll be joined by Mike Girvin from Excel is Fun.
This is our Duel number 193. Add a number to cells with common delimited numbers.
Power Query Edition. All right.
Same question as last week's episode 192.
Siva has a cell with a bunch of numbers separated by commas. He wants to add another number to all of those numbers.
Last week, I did this with VBA. Mike did it with a LET formula.
But it could have been done with Power Query.
As Mike and I talked about this, we each had a completely different Power Query way to go. So here's my power query solution.
I am going to use Ctrl+T to make this into a table.Then Data, From Table or Range.
You see Power Query changes this to scientific notation.
So I remove the Change Type step from the applied steps.
Under Add Column, I am going to add an index column.
Choose "From 1". Take this original data and Home, Split Column by Delimiter.
They are smart enough to figure out it's a comma.
But, here under advanced options I am going to say I want to split it into Rows. Click OK.
So now we have a list of all of those numbers broken out.
But we know which record they belong to, thanks to the Index column.
Under Add Column, I am going to create a Custom Column. I will call it New Total.
The formula is going to be equal to the Original plus the Value to Add. Click OK.
So we originally had 1444. We want to add 400. That is 1844.
Down in record two, 1190 plus 300 gives 1490. That is working great.
At this point, we don't need these columns anymore. Right-click and Remove Columns.
Now, this is the point that if I I had DAX, I would be using the CONCATENATEX function.
I learned this step because it didn't work the first time.
I am going to take that New Total column. This is completely counter-intuitive.
Those are numbers clearly. I am going to Transform that from Type=Any to Type=Text!
Then I am going to Group by the Index column.
I am going to create a new Dummy column.
The operation is going to be summing those text values in New Total.
Of course, this is not going to work!
But it provides a step up here in the formula bar that I can edit. There's my Dummy column.
I am going to change List Dot Sum to Text Dot Combine.
That's the function I need. It is equivalent to the DAX CONCATENATEX.
It's not available in the Power Query user interface.
But by using the Sum function I can then get to the point where I can edit that. Now Text.Combine has a second argument.
We get to say what the delimiter is.
So in quotes, type a comma. Click away. There we are.
Okay so for record number one: 1844, 1845, 1846.
It looks great, I don't need the index column anymore. I will remove that. Then Home, Close and Load To..
I want it to go in the same spreadsheet. So, right here, choose into an existing worksheet.
We'll put it right next to the old data. Right there. Click OK.
And there it is. Power Query, by just editing one line in the formula bar.
Everything else is straight from the user interface.
Mike, let's see what you have. [ Mike Girvin: ] Thanks, MrExcel.
All of these steps created with the user interface and one edit.
It is probably going to calculate more quickly and efficiently than my single column solution. Nevertheless, let's check this out.
We are going to do this with a single formula.
Now, I already have this converted to an Excel Table.
I could come up to From Table but i already have the button added to the QAT.
Of course, you can right click any button and choose Add To The Quick Access Toolbar.
So now all I do is hit the Alt key and that's the number 9.
So it is Alt+9 and I get that table inside of the Power Query editor.
I am going to change the name. Delete this step.
We don't want it as a number. Now I go up to Add Column. Custom column.
And when I use the custom column dialog box, it is going to add a new step here.
And it will also add the function Table.AddColumn. We'll see it up in the formula bar.
What that function does is that it will iterate over each row in the table and perform some action.
What we want to do is split by the comma delimiter to get a list of numbers, that we can use to add the number from the Value To Add column. The new column - we will call it NewText.
And guess what?
Right inside of Table.AddColumn we will use another function: Table.Split. Now this is the name of the column.
We can see it in the table. So because we're inside of Table.Add columns, we are allowed to just list the column name in square brackets. And then we type comma.
And the delimiter will be in double quotes, a comma.
Now this will create a list for each row in this table. So when I click OK, we have created a list.
in each row in the table. Now think about this.
In the Excel worksheet, we are allowed to have numbers and text and dates.
But over here in Power Query, we are allowed to work on many different objects including a list like this.
Tables, records and many other types of objects.
Now we have our list that is being created by the Table.AddColumn function. The keyword "Each" means that Table.AddColumn is running a custom function for each row in the table that we created.
So far all we have is Text.Split.
Now because we use the keyword "each", we are allowed to simply put the column name in square brackets.
For now, that will work. The next step is we need to convert this list.
Which means, for each cell, we are going to be doing a second iteration on the actual list.
So after the keyword "each", we are going to type List.Transform.
Open parentheses. There's the list.
I am going to come to the end and backspace and then type a comma.
And notice the second argument of List.Transform requires a function.
That means we are going to end up getting in trouble because we're iterating once with Table.AddColumn and now we're iterating a second time.
Now, because we're iterating in List.Transform.
We can use "each" for the time being and if i'm using "each: I can use the shorthand underscore to get each item from that list.
Now each of these items is considered text and if we need to add an actual number, that's not going to work.
In power query, unlike the Excel worksheet, the operation of adding will not convert a text number to a number. So we're going to have to explicitly.
convert these text items to a number and the function we can use is Number.From.
Open parentheses, the underscore gets each item from that list.
Close parentheses.
Now, so far we can get away with the two "each" keywords because the underscore is acting on Table.Split.
And that original column is working with Table.AddColumn so when I type close parentheses, close parentheses and hit enter.
Now if we look at our list we can't visually tell that it's a number. But it is.
So the list in this row -- they are all numbers. We simply need to add the 505.
So we come up to the formula bar and we're going to try after number dot from plus and we use our field access operators to get to a particular field.
It is case sensitive so we have to spell everything in case correctly.
and here's the problem.
Everything that comes after this "each" has to be acting on Text.Split.
And that Value.Add?
That's a column in this table that Table.AddColumns is governing.
This is where the two keywords "each" get in our way.
So instead of using the shorthand, we will explicitly define a variable for this function in Table.AddColumn. We define a variable with parentheses.
We are going to call this variable OT inside parentheses.
And then the go to operator is =>.
So now instead of using each and just putting the actual column name, we have to preface whatever columns we use with that OT.
And the advantage is obvious.
When we define an explicit variable, we are allowed to use the columns from the outside table anywhere in our formula.
"Each" is just when you want an easy way to iterate and you're not going back and forth between multiple iterators. So now, if i come to the end.
Close on List.Transform. Close on Table.AddColumn.
There is the new numbers, Now, these are numbers.
If we're going to use the Text.Combine function they cannot be numbers.
So now we use Text.From and then right after the column, the outside column I close parentheses.
Now, if i hit Enter, we won't see anything different but now I am allowed for that full list of text items to use Text.Combine.
Open parentheses Very carefully at the end, press comma and the second argument for text dot combine is "," Close. Close. And there's our formula.
Now I can right-click. Remove other columns. Home, Close and Load, Close and Load to.
Existing sheet. That cell. Click OK.
And there with a single column, a single formula. We have added a number to a text string.
And then recombined everything into a single cell.
Now ultimately the problem with the single cell formula is we're doing lots of iterating including iterating down this table and then for each row in the table.
Iterating and sometimes formulas like that will take longer to calculate than using the user interface.
Now for this example with a small data set it's no problem.
But on bigger data sets it can be a problem. All right. I will throw it back to MrExcel.
[ Bill Jelen: ] You know, Mike.
Once, long ago, I was at the Model Off championships.
They had a little game where whoever wrote the shortest formula was the winner.
They were playing for $30,000 or some big number.
So I love that a lot of people commented on your channel and on my channel with their favorite methods.
I kind of arranged them here. Your Power Query definitely wins.
It is just an awesome power query. It is interesting though.
We both saw this solution from Abiola David.
We both simultaneously on Tuesday released a video showing that method.
People watching your channel should go check this out.
People watching my channel should go check this out.
It is probably the one that ran circles around both of us.
But really, no matter how long your solution was, everyone who sent in a solution is a winner because Siva had this problem. He didn't know how to solve the problem.
And we've all come up with 15 different solutions here that will solve that problem. So thanks to everyone who commented.
and Mike this was another great two-week duel. That just that shows how awesome Excel is.
All these different ways to solve this problem.
I want to thank everyone for stopping by.
We'll see you next time for another Dueling Excel podcast from MrExcel and ExcelisFun.
I am Bill Jelen from MrExcel. We'll be joined by Mike Girvin from Excel is Fun.
This is our Duel number 193. Add a number to cells with common delimited numbers.
Power Query Edition. All right.
Same question as last week's episode 192.
Siva has a cell with a bunch of numbers separated by commas. He wants to add another number to all of those numbers.
Last week, I did this with VBA. Mike did it with a LET formula.
But it could have been done with Power Query.
As Mike and I talked about this, we each had a completely different Power Query way to go. So here's my power query solution.
I am going to use Ctrl+T to make this into a table.Then Data, From Table or Range.
You see Power Query changes this to scientific notation.
So I remove the Change Type step from the applied steps.
Under Add Column, I am going to add an index column.
Choose "From 1". Take this original data and Home, Split Column by Delimiter.
They are smart enough to figure out it's a comma.
But, here under advanced options I am going to say I want to split it into Rows. Click OK.
So now we have a list of all of those numbers broken out.
But we know which record they belong to, thanks to the Index column.
Under Add Column, I am going to create a Custom Column. I will call it New Total.
The formula is going to be equal to the Original plus the Value to Add. Click OK.
So we originally had 1444. We want to add 400. That is 1844.
Down in record two, 1190 plus 300 gives 1490. That is working great.
At this point, we don't need these columns anymore. Right-click and Remove Columns.
Now, this is the point that if I I had DAX, I would be using the CONCATENATEX function.
I learned this step because it didn't work the first time.
I am going to take that New Total column. This is completely counter-intuitive.
Those are numbers clearly. I am going to Transform that from Type=Any to Type=Text!
Then I am going to Group by the Index column.
I am going to create a new Dummy column.
The operation is going to be summing those text values in New Total.
Of course, this is not going to work!
But it provides a step up here in the formula bar that I can edit. There's my Dummy column.
I am going to change List Dot Sum to Text Dot Combine.
That's the function I need. It is equivalent to the DAX CONCATENATEX.
It's not available in the Power Query user interface.
But by using the Sum function I can then get to the point where I can edit that. Now Text.Combine has a second argument.
We get to say what the delimiter is.
So in quotes, type a comma. Click away. There we are.
Okay so for record number one: 1844, 1845, 1846.
It looks great, I don't need the index column anymore. I will remove that. Then Home, Close and Load To..
I want it to go in the same spreadsheet. So, right here, choose into an existing worksheet.
We'll put it right next to the old data. Right there. Click OK.
And there it is. Power Query, by just editing one line in the formula bar.
Everything else is straight from the user interface.
Mike, let's see what you have. [ Mike Girvin: ] Thanks, MrExcel.
All of these steps created with the user interface and one edit.
It is probably going to calculate more quickly and efficiently than my single column solution. Nevertheless, let's check this out.
We are going to do this with a single formula.
Now, I already have this converted to an Excel Table.
I could come up to From Table but i already have the button added to the QAT.
Of course, you can right click any button and choose Add To The Quick Access Toolbar.
So now all I do is hit the Alt key and that's the number 9.
So it is Alt+9 and I get that table inside of the Power Query editor.
I am going to change the name. Delete this step.
We don't want it as a number. Now I go up to Add Column. Custom column.
And when I use the custom column dialog box, it is going to add a new step here.
And it will also add the function Table.AddColumn. We'll see it up in the formula bar.
What that function does is that it will iterate over each row in the table and perform some action.
What we want to do is split by the comma delimiter to get a list of numbers, that we can use to add the number from the Value To Add column. The new column - we will call it NewText.
And guess what?
Right inside of Table.AddColumn we will use another function: Table.Split. Now this is the name of the column.
We can see it in the table. So because we're inside of Table.Add columns, we are allowed to just list the column name in square brackets. And then we type comma.
And the delimiter will be in double quotes, a comma.
Now this will create a list for each row in this table. So when I click OK, we have created a list.
in each row in the table. Now think about this.
In the Excel worksheet, we are allowed to have numbers and text and dates.
But over here in Power Query, we are allowed to work on many different objects including a list like this.
Tables, records and many other types of objects.
Now we have our list that is being created by the Table.AddColumn function. The keyword "Each" means that Table.AddColumn is running a custom function for each row in the table that we created.
So far all we have is Text.Split.
Now because we use the keyword "each", we are allowed to simply put the column name in square brackets.
For now, that will work. The next step is we need to convert this list.
Which means, for each cell, we are going to be doing a second iteration on the actual list.
So after the keyword "each", we are going to type List.Transform.
Open parentheses. There's the list.
I am going to come to the end and backspace and then type a comma.
And notice the second argument of List.Transform requires a function.
That means we are going to end up getting in trouble because we're iterating once with Table.AddColumn and now we're iterating a second time.
Now, because we're iterating in List.Transform.
We can use "each" for the time being and if i'm using "each: I can use the shorthand underscore to get each item from that list.
Now each of these items is considered text and if we need to add an actual number, that's not going to work.
In power query, unlike the Excel worksheet, the operation of adding will not convert a text number to a number. So we're going to have to explicitly.
convert these text items to a number and the function we can use is Number.From.
Open parentheses, the underscore gets each item from that list.
Close parentheses.
Now, so far we can get away with the two "each" keywords because the underscore is acting on Table.Split.
And that original column is working with Table.AddColumn so when I type close parentheses, close parentheses and hit enter.
Now if we look at our list we can't visually tell that it's a number. But it is.
So the list in this row -- they are all numbers. We simply need to add the 505.
So we come up to the formula bar and we're going to try after number dot from plus and we use our field access operators to get to a particular field.
It is case sensitive so we have to spell everything in case correctly.
and here's the problem.
Everything that comes after this "each" has to be acting on Text.Split.
And that Value.Add?
That's a column in this table that Table.AddColumns is governing.
This is where the two keywords "each" get in our way.
So instead of using the shorthand, we will explicitly define a variable for this function in Table.AddColumn. We define a variable with parentheses.
We are going to call this variable OT inside parentheses.
And then the go to operator is =>.
So now instead of using each and just putting the actual column name, we have to preface whatever columns we use with that OT.
And the advantage is obvious.
When we define an explicit variable, we are allowed to use the columns from the outside table anywhere in our formula.
"Each" is just when you want an easy way to iterate and you're not going back and forth between multiple iterators. So now, if i come to the end.
Close on List.Transform. Close on Table.AddColumn.
There is the new numbers, Now, these are numbers.
If we're going to use the Text.Combine function they cannot be numbers.
So now we use Text.From and then right after the column, the outside column I close parentheses.
Now, if i hit Enter, we won't see anything different but now I am allowed for that full list of text items to use Text.Combine.
Open parentheses Very carefully at the end, press comma and the second argument for text dot combine is "," Close. Close. And there's our formula.
Now I can right-click. Remove other columns. Home, Close and Load, Close and Load to.
Existing sheet. That cell. Click OK.
And there with a single column, a single formula. We have added a number to a text string.
And then recombined everything into a single cell.
Now ultimately the problem with the single cell formula is we're doing lots of iterating including iterating down this table and then for each row in the table.
Iterating and sometimes formulas like that will take longer to calculate than using the user interface.
Now for this example with a small data set it's no problem.
But on bigger data sets it can be a problem. All right. I will throw it back to MrExcel.
[ Bill Jelen: ] You know, Mike.
Once, long ago, I was at the Model Off championships.
They had a little game where whoever wrote the shortest formula was the winner.
They were playing for $30,000 or some big number.
So I love that a lot of people commented on your channel and on my channel with their favorite methods.
I kind of arranged them here. Your Power Query definitely wins.
It is just an awesome power query. It is interesting though.
We both saw this solution from Abiola David.
We both simultaneously on Tuesday released a video showing that method.
People watching your channel should go check this out.
People watching my channel should go check this out.
It is probably the one that ran circles around both of us.
But really, no matter how long your solution was, everyone who sent in a solution is a winner because Siva had this problem. He didn't know how to solve the problem.
And we've all come up with 15 different solutions here that will solve that problem. So thanks to everyone who commented.
and Mike this was another great two-week duel. That just that shows how awesome Excel is.
All these different ways to solve this problem.
I want to thank everyone for stopping by.
We'll see you next time for another Dueling Excel podcast from MrExcel and ExcelisFun.
