Laura B has a tricky Power Query question today. She needs to delete all rows where B=C and D=E. Bill solves this in the Power Query Editor but knows it is not efficient.
He turns to ChatGPT - an Artificial Intelligence tool that is in a free research preview. Bill is amazed at how quickly ChatGPT writes M code for Power Query. But then he bangs his head against the wall trying to get it to work.
See why Stack Overflow has banned ChatGPT because it generates code that is usually wrong but looks like it could work.
Finally, we turn to Smozgur, author of the upcoming "You wouldn't write professional VBA code using the macro recorder would you? A Programmer's Guide to M" book for his efficient solution.
As a bonus, Bill shows ChatGPT's attempts to write Excel formulas. Bill is convinced that his late Uncle B's is secretly running ChatGPT from the great beyond.
Table of Contents
(0:00) 3 Ways to Solve Power Query Problem
(0:29) Human versus ChatGPT
(0:55) What is ChatGPT
(2:44) StackOverflow bans ChatGPT
(3:49) Tricky Power Query problem from Laura
(4:32) Power Query to delete based on 2 conditions
(5:00) Power Query Add Conditional Column
(7:34) Asking ChatGPT for Power Query code
(8:12) ChatGPT is AMAZING!
(8:28) Adjusting ChatGPT Code
(8:51) Why ChatGPT code sucked
(9:45) Efficient M code for 2 conditions
(10:44) ChatGPT success with IF AND formula
(11:03) ChatGPT fails for Prime number formula
(11:21) ChatGPT fails again.
(11:44) Uncle B's ability to talk about anything
(12:19) ChatGPT suggests the fictional ISPRIME function
(12:45) 25% success rate. Not impressed.
(13:15) Nancy plays us out
He turns to ChatGPT - an Artificial Intelligence tool that is in a free research preview. Bill is amazed at how quickly ChatGPT writes M code for Power Query. But then he bangs his head against the wall trying to get it to work.
See why Stack Overflow has banned ChatGPT because it generates code that is usually wrong but looks like it could work.
Finally, we turn to Smozgur, author of the upcoming "You wouldn't write professional VBA code using the macro recorder would you? A Programmer's Guide to M" book for his efficient solution.
As a bonus, Bill shows ChatGPT's attempts to write Excel formulas. Bill is convinced that his late Uncle B's is secretly running ChatGPT from the great beyond.
Table of Contents
(0:00) 3 Ways to Solve Power Query Problem
(0:29) Human versus ChatGPT
(0:55) What is ChatGPT
(2:44) StackOverflow bans ChatGPT
(3:49) Tricky Power Query problem from Laura
(4:32) Power Query to delete based on 2 conditions
(5:00) Power Query Add Conditional Column
(7:34) Asking ChatGPT for Power Query code
(8:12) ChatGPT is AMAZING!
(8:28) Adjusting ChatGPT Code
(8:51) Why ChatGPT code sucked
(9:45) Efficient M code for 2 conditions
(10:44) ChatGPT success with IF AND formula
(11:03) ChatGPT fails for Prime number formula
(11:21) ChatGPT fails again.
(11:44) Uncle B's ability to talk about anything
(12:19) ChatGPT suggests the fictional ISPRIME function
(12:45) 25% success rate. Not impressed.
(13:15) Nancy plays us out
Transcript of the video:
Learn Excel from MrExcel podcast episode 2355. Can ChatGPT write Power Query code?
Let's change that. Can ChatGPT write useful Power Query code?
Or can ChatGPT write working Power Query code?
Let's do this.
Learn Excel from MrExcel podcast episode 2355: Can Power Query delete records when both column C equals D and E equals F.
Today's test, a Power Query question from Laura B and you're going to see three answers.
A novice, I know that my code is not efficient, and then this amazing new thing that everyone's talking about, ChatGPT, and then an actual expert who knows what they're doing.
So in this video, you're going to see three solutions to a Power Query question, and I'm sure you will have other solutions and we'll take a look at why all the hype about ChatGPT?
Am I really missing something? All right, so it's a video about ChatGPT.
Just a few nights ago on Jimmy Kimmel, he said, "Reviews of brand-new technology never age well".
I'm recording this on January 12th, 2023, using tests I did on January 1st, January 11th and January 12th.
If you're watching this a year from now or 10 years from now, ChatGPT has taken over the world or are our robotic overlords.
And sorry if I wasn't completely positive today.
ChatGPT even says, "This is a free research preview.
Your feedback will help us improve". They say they're not perfect.
But I mean a lot of people are talking about, hey, it can create Excel formulas, it can write VBA code, it can write Power Query code. Write blog posts, optimize blog posts.
I've written 67 books. Maybe it'll write my 68th book, who knows?
But the thing is, I hate to even show it right now because if you go and try it, on January 10th and 11th, I got this screen. "ChatGPT is at capacity right now.
Get notified when we're back".
So if you don't already have an account, you can't even get an account and hopefully they'll figure out a way to solve that.
Really interesting.
At the MrExcel message board, the community there has answered over a million Excel questions.
And right here, I think January 4th, 2023, is the first time that someone popped in here and said, "Hey, I actually used ChatGPT and it didn't know to put the sub dims and end sub in.
But the rest of the code that it gave me for this simple little problem, "Write code that removes all rows in a column which either have a value that is bold or empty". It worked, right.?
I mean, that's impressive. A little AI that's able to generate some VBA code.
But also super interesting.
Over at Stack Overflow, currently they've temporarily banned the posting of ChatGPT answers.
And they explain it, it's a great explanation, and boy I can ... this resonates with me.
"ChatGPT produces answers that have a high rate of being incorrect, although they look like they might be good".
When I was recording this episode, when it gave me Power Query, I was so excited.
I can't even post it because I was like, "Oh my God, this is amazing".
It looked like it was going to work. But here's the problem.
These answers are so easy to produce.
A lot of people started using ChatGPT to post answers at Stack Overflow, like thousands of answers.
But then you need someone who knows what they're doing to do a detailed read with at least some subject matter expertise in order to determine that the answer is actually bad.
So right now, Stack Overflow has banned ChatGPT. All right, so that's a little overview of ChatGPT.
Let's get to today's problem. Laura sent in this question.
Laura had been in my class and seen me do an intro to Power Query.
She was like, "Okay, I'm trying to use Power Query, but here's what I have to do.
I have to find every record where C equals D and where E equals F.
If that's true, I want to delete those rows.
And I knew I could solve this in Power Query but I knew that I would solve it badly". Laura even was nice enough to mark the rows that were the match.
And of course she has a much larger dataset than this.
This was just a small sample.
So first step here is I'm going to solve this in Power Query using the Power Query interface.
We have a table with a name called data, all right. So data from table [ inaudible 00:04:39 ].
And ultimately at the end I want to filter out the rows where item status equals item type, part number equals base part.
But the problem that I have is that I can't write that in M code.
I'm going to have to use the Power Query interface.
And so looking for item status equals item type, that's a conditional column. So add column, conditional column.
Easy enough to do this. We'll call this C equals D.
It doesn't matter what you call it. You could've just left it the way it was.
And so we're going to say if item status is equal to not a particular value, but a column.
If item status is equal to item type, then out here we're going to just put something, like a one. I'm going to put a one, otherwise a zero.
Now I have a second clause, an and clause.
And that and clause, as I say, if part number's equal to base part.
But here in the Power Query window, the only thing I can do is an else if, right? That's not what I'm looking for.
I need an and. All right.
So at this point, I'm already frustrated.
I know that this is going to be a horribly painful solution.
So we have C equals D out there and then a second column that's going to be called E is F.
And this is going to say, is the part number equal to the base part. Then put a one, otherwise zero.
Click okay. All right.
And this seems really silly. Are both true?
And so we're going to say this first thing, C equals D plus E is F.
Click okay. All right.
And now anything out here that has a two are the ones that we want to delete.
Simple enough.
Open the filter drop down, clear the two, click okay.
We don't need any of these columns that I added so all of these ... Click shift click, right click, remove columns.
And we end up with this code, all of these extra steps.
So we added a conditional column, added a second conditional column, added a custom column, filter the rows, remove the columns, ends up being 10 rows of Power Query. And I feel bad.
I know that this is not the most efficient way to write this, and frankly, I'm just too lazy to go get Ken Puls' book, Master Your Data on Power Query, to go figure out how to write that.
Sorry Laura, that's my solution. But then, I had seen ChatGPT write VBA code.
I had personally used it to create some formulas and I'd heard that it could do Power Query code.
So I was like, let's see if ChatGPT can improve my code.
Here we are.
I'll add a new chat down here. And we'll send.
"Here's an example of the Power Query M code that can be used to remove rows from the table named data if both conditions are true". Look at this.
Holy smokes, that's amazing. No, stop.
I was prematurely excited.
I'm just cutting the next seven minutes that happened. Let's summarize it like this.
So here's what I asked. Here's what they gave me.
I was excited about it.
I could tell that their let source equals data wasn't enough and I knew enough to take the code from my original query and change the source, add in change type and then use that variable name there.
But the code still doesn't work.
But you see, now I'm in trouble because now I have code that I didn't write, I don't understand, and I have no clue where to go.
So I turned to my subject matter expert, Smozgur, and he took a look at the code.
He says, "This code isn't going to work, right?
Table remove rows will remove 10 rows starting from the sixth row by using the following code". But ChatGPT only gave two parameters.
So basically here that two would remove the third row because it's zero based counting.
But they're not passing it a number.
They're passing it a variable that's holding a table and that's not compatible with table remove rows.
And it's totally not the right function to use here.
And beyond that, doing the filter one row a time is very inefficient.
And life pro tip, by putting a calculation in the end clause, we can't see that result.
It makes it very hard to debug. So just a complete fail here from ChatGPT.
So back to Laura's question, "What's the efficient way to write this code?" First off, from Boolean logic, we know that if we're trying to delete where A equals B and C equals D, you can keep where either of those are not the same. So it's the same logic there.
And so we have the source and change type exactly from the Power Query editor, although Smozgur is always careful to get rid of those spaces, which means we don't need the quotes and the hash sign anymore. Result equals table and select rows.
Each item type not equal to item status or part number not equal to base part.
Now, as far as I know, there's no way to build this in the Power Query editor.
Of course, someone down in the YouTube comments will let me know that there's a secret hidden thing if I hover over this invisible icon that frees that up.
So there's the code, Laura, that would work efficiently and you just need to go into the advanced editor to create that. And as far as ChatGPT, just some quick tests.
I was very impressed.
One of the first tests I did back in January 1st is a question from one of my Power Excel seminars, and it did a great job of generating an if with an and function inside. And that was beautiful.
And then I hit it with, all right, give me a formula test if the value in A2 is a prime number.
And I was impressed that they came up with this.
This was their answer on January 1st and they caution me that it only works for small numbers.
But when I actually tested it, it doesn't work for three or for five. So kind of a fail there.
And then this one is really wild.
On January 11th, I asked the same question again, and there's several ways to test if the value in A2 is a prime number, but one common method is to use the following formula, which looked pretty clever but it doesn't work.
And then, look, I had this uncle.
When I was a kid, I would always go over to the uncle's house and he would speak with authority on any topic, even if he had no clue what he was talking about. You have the same uncle, right?
I'm going to change his name. I'm going to call him Uncle Bs.
You would be there and you'd be so impressed.
And then we'd be driving home, my dad would be like, "That guy is so full of ****". And if you fact-checked anything, it wasn't true.
I think that my late Uncle Bs is secretly the guy behind ChatGPT.
Somehow he's come back from the great beyond and is running things.
Because now, on January 11th, ChatGPT then goes on to say another way is to use the is prime function in Excel.
And even with authority says that it's only available in Office 365 or later.
********. It's not available at all.
This is not a function.
Why do all of these AIs keep thinking that is prime is a function? Does anyone out there have is prime?
Is there some top secret beta that I'm not aware of?
Yeah. All right.
So look, I got one of my four queries, right.
This will improve, there will come a day where it puts us all out of work.
Yeah.
But right now, January 12th, 2023, you have to know what the heck you're doing if you're going to use this.
You have to know the answer before you go to ChatGPT and ask them for the answer.
And on the days where it's just over capacity, too bad.
So today, not impressed. Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Let's change that. Can ChatGPT write useful Power Query code?
Or can ChatGPT write working Power Query code?
Let's do this.
Learn Excel from MrExcel podcast episode 2355: Can Power Query delete records when both column C equals D and E equals F.
Today's test, a Power Query question from Laura B and you're going to see three answers.
A novice, I know that my code is not efficient, and then this amazing new thing that everyone's talking about, ChatGPT, and then an actual expert who knows what they're doing.
So in this video, you're going to see three solutions to a Power Query question, and I'm sure you will have other solutions and we'll take a look at why all the hype about ChatGPT?
Am I really missing something? All right, so it's a video about ChatGPT.
Just a few nights ago on Jimmy Kimmel, he said, "Reviews of brand-new technology never age well".
I'm recording this on January 12th, 2023, using tests I did on January 1st, January 11th and January 12th.
If you're watching this a year from now or 10 years from now, ChatGPT has taken over the world or are our robotic overlords.
And sorry if I wasn't completely positive today.
ChatGPT even says, "This is a free research preview.
Your feedback will help us improve". They say they're not perfect.
But I mean a lot of people are talking about, hey, it can create Excel formulas, it can write VBA code, it can write Power Query code. Write blog posts, optimize blog posts.
I've written 67 books. Maybe it'll write my 68th book, who knows?
But the thing is, I hate to even show it right now because if you go and try it, on January 10th and 11th, I got this screen. "ChatGPT is at capacity right now.
Get notified when we're back".
So if you don't already have an account, you can't even get an account and hopefully they'll figure out a way to solve that.
Really interesting.
At the MrExcel message board, the community there has answered over a million Excel questions.
And right here, I think January 4th, 2023, is the first time that someone popped in here and said, "Hey, I actually used ChatGPT and it didn't know to put the sub dims and end sub in.
But the rest of the code that it gave me for this simple little problem, "Write code that removes all rows in a column which either have a value that is bold or empty". It worked, right.?
I mean, that's impressive. A little AI that's able to generate some VBA code.
But also super interesting.
Over at Stack Overflow, currently they've temporarily banned the posting of ChatGPT answers.
And they explain it, it's a great explanation, and boy I can ... this resonates with me.
"ChatGPT produces answers that have a high rate of being incorrect, although they look like they might be good".
When I was recording this episode, when it gave me Power Query, I was so excited.
I can't even post it because I was like, "Oh my God, this is amazing".
It looked like it was going to work. But here's the problem.
These answers are so easy to produce.
A lot of people started using ChatGPT to post answers at Stack Overflow, like thousands of answers.
But then you need someone who knows what they're doing to do a detailed read with at least some subject matter expertise in order to determine that the answer is actually bad.
So right now, Stack Overflow has banned ChatGPT. All right, so that's a little overview of ChatGPT.
Let's get to today's problem. Laura sent in this question.
Laura had been in my class and seen me do an intro to Power Query.
She was like, "Okay, I'm trying to use Power Query, but here's what I have to do.
I have to find every record where C equals D and where E equals F.
If that's true, I want to delete those rows.
And I knew I could solve this in Power Query but I knew that I would solve it badly". Laura even was nice enough to mark the rows that were the match.
And of course she has a much larger dataset than this.
This was just a small sample.
So first step here is I'm going to solve this in Power Query using the Power Query interface.
We have a table with a name called data, all right. So data from table [ inaudible 00:04:39 ].
And ultimately at the end I want to filter out the rows where item status equals item type, part number equals base part.
But the problem that I have is that I can't write that in M code.
I'm going to have to use the Power Query interface.
And so looking for item status equals item type, that's a conditional column. So add column, conditional column.
Easy enough to do this. We'll call this C equals D.
It doesn't matter what you call it. You could've just left it the way it was.
And so we're going to say if item status is equal to not a particular value, but a column.
If item status is equal to item type, then out here we're going to just put something, like a one. I'm going to put a one, otherwise a zero.
Now I have a second clause, an and clause.
And that and clause, as I say, if part number's equal to base part.
But here in the Power Query window, the only thing I can do is an else if, right? That's not what I'm looking for.
I need an and. All right.
So at this point, I'm already frustrated.
I know that this is going to be a horribly painful solution.
So we have C equals D out there and then a second column that's going to be called E is F.
And this is going to say, is the part number equal to the base part. Then put a one, otherwise zero.
Click okay. All right.
And this seems really silly. Are both true?
And so we're going to say this first thing, C equals D plus E is F.
Click okay. All right.
And now anything out here that has a two are the ones that we want to delete.
Simple enough.
Open the filter drop down, clear the two, click okay.
We don't need any of these columns that I added so all of these ... Click shift click, right click, remove columns.
And we end up with this code, all of these extra steps.
So we added a conditional column, added a second conditional column, added a custom column, filter the rows, remove the columns, ends up being 10 rows of Power Query. And I feel bad.
I know that this is not the most efficient way to write this, and frankly, I'm just too lazy to go get Ken Puls' book, Master Your Data on Power Query, to go figure out how to write that.
Sorry Laura, that's my solution. But then, I had seen ChatGPT write VBA code.
I had personally used it to create some formulas and I'd heard that it could do Power Query code.
So I was like, let's see if ChatGPT can improve my code.
Here we are.
I'll add a new chat down here. And we'll send.
"Here's an example of the Power Query M code that can be used to remove rows from the table named data if both conditions are true". Look at this.
Holy smokes, that's amazing. No, stop.
I was prematurely excited.
I'm just cutting the next seven minutes that happened. Let's summarize it like this.
So here's what I asked. Here's what they gave me.
I was excited about it.
I could tell that their let source equals data wasn't enough and I knew enough to take the code from my original query and change the source, add in change type and then use that variable name there.
But the code still doesn't work.
But you see, now I'm in trouble because now I have code that I didn't write, I don't understand, and I have no clue where to go.
So I turned to my subject matter expert, Smozgur, and he took a look at the code.
He says, "This code isn't going to work, right?
Table remove rows will remove 10 rows starting from the sixth row by using the following code". But ChatGPT only gave two parameters.
So basically here that two would remove the third row because it's zero based counting.
But they're not passing it a number.
They're passing it a variable that's holding a table and that's not compatible with table remove rows.
And it's totally not the right function to use here.
And beyond that, doing the filter one row a time is very inefficient.
And life pro tip, by putting a calculation in the end clause, we can't see that result.
It makes it very hard to debug. So just a complete fail here from ChatGPT.
So back to Laura's question, "What's the efficient way to write this code?" First off, from Boolean logic, we know that if we're trying to delete where A equals B and C equals D, you can keep where either of those are not the same. So it's the same logic there.
And so we have the source and change type exactly from the Power Query editor, although Smozgur is always careful to get rid of those spaces, which means we don't need the quotes and the hash sign anymore. Result equals table and select rows.
Each item type not equal to item status or part number not equal to base part.
Now, as far as I know, there's no way to build this in the Power Query editor.
Of course, someone down in the YouTube comments will let me know that there's a secret hidden thing if I hover over this invisible icon that frees that up.
So there's the code, Laura, that would work efficiently and you just need to go into the advanced editor to create that. And as far as ChatGPT, just some quick tests.
I was very impressed.
One of the first tests I did back in January 1st is a question from one of my Power Excel seminars, and it did a great job of generating an if with an and function inside. And that was beautiful.
And then I hit it with, all right, give me a formula test if the value in A2 is a prime number.
And I was impressed that they came up with this.
This was their answer on January 1st and they caution me that it only works for small numbers.
But when I actually tested it, it doesn't work for three or for five. So kind of a fail there.
And then this one is really wild.
On January 11th, I asked the same question again, and there's several ways to test if the value in A2 is a prime number, but one common method is to use the following formula, which looked pretty clever but it doesn't work.
And then, look, I had this uncle.
When I was a kid, I would always go over to the uncle's house and he would speak with authority on any topic, even if he had no clue what he was talking about. You have the same uncle, right?
I'm going to change his name. I'm going to call him Uncle Bs.
You would be there and you'd be so impressed.
And then we'd be driving home, my dad would be like, "That guy is so full of ****". And if you fact-checked anything, it wasn't true.
I think that my late Uncle Bs is secretly the guy behind ChatGPT.
Somehow he's come back from the great beyond and is running things.
Because now, on January 11th, ChatGPT then goes on to say another way is to use the is prime function in Excel.
And even with authority says that it's only available in Office 365 or later.
********. It's not available at all.
This is not a function.
Why do all of these AIs keep thinking that is prime is a function? Does anyone out there have is prime?
Is there some top secret beta that I'm not aware of?
Yeah. All right.
So look, I got one of my four queries, right.
This will improve, there will come a day where it puts us all out of work.
Yeah.
But right now, January 12th, 2023, you have to know what the heck you're doing if you're going to use this.
You have to know the answer before you go to ChatGPT and ask them for the answer.
And on the days where it's just over capacity, too bad.
So today, not impressed. Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
