I was trying to NOT do M Code in this one because my target was beginners. But for us, it is MUCH easier to to do exactly what you say: just quickly amend M Code Table.Combine : ) Thanks, for the hot tip : ) : ) : ) : )
@@excelisfun Aha, exactly: you DID start off with a “typie” (and that is not a typo). ;-) And frankly, I find doing a little typie (read as “tie-pee”) where you amend the formula by continuing the pattern, quite intuitive. You may even sell it as “cheating” a little bit. Some people like that attitude. I just call it: elegant. [crosses arms, looks into the distance and pauses for effect] Now, don’t get me wrong: I love the PQ-M interface to pieces, but we Exceleers [shameless plug] are used to the formula bar, so why not play with it once in awhile. :-) Oh man, how far have we sunk, I mean: come, I said: come. I mean that we now take this playing for granted. It’s friday - humor me. :-)
@@GeertDelmulle You are right about selling it as cheatings. I must have 500 examples of saying exactly that lol BUT: what I really should say from now on is now we're gunna cheat here, urr, I mean, make it elegant lol^2 And you are right, we Exceleers ARE used to formula, so why not ; ) Okay, since it's Friday: here is a joke to humor you: wait, I am bad with jokes and don't know any. Well, in lieu of that, we will just bask in the rad glory of being able to play and have fun!!!! Go Team!
@@excelisfun Mike, you know: the other day one of my more hairy and muscular colleagues was making fun of me on a conf.call, and said that all that Excel is all well and good, but, he asked -especially now in the lockdown- if I was doing any lifting? [paused for effect] In contrast to what he expected, my response was quick and enthousiastic: Me: Oh, yeah, sure, ever since February 2020 I’m used to lifting. All. The. Time. I even do Pairwise Lifting. It’s the first thing I do in the morning. Especially because of Corona. [paused for effect, myself] He: Really?!? OK, hey, I didn’t know that... And we left it at that. He had NO IDEA. He didn’t get the joke,... but I’m sure YOU do. . . . . PS: Yes, this was a joke - I don’t have any hairy, muscular colleagues (that I know of) and that conversation never happened. You know I can’t lie. :-)
I am so consumed by my job during covid 19 and with extra family duties, while trying to finish my book (which is 6 months behind), but in about a year after I finish my book and the associated videos, I either plan to write a book about Power Query M Code or concentrate on videos about that. The real problem is that life keeps getting in the way of all the Excel fun that I want to do... : (
Thank you Mike i love excel because of you :). I also watch your amazing video that cover pivot table is there any video that explain all aggregate operations that appear in the pivot table menu
Hi Mike. Awesome lesson.. as always! Power Query is such a powerful and useful feature to know. Thanks for the examples. In keeping with this theme, I upgraded my UDF to allow for appending from 1 to 5 tables.. had a friend who liked the first version, but wanted to have the option to use it on something other than exactly 3 tables. Back to the drawing board and a small adjustment gives the user the option of appending from 1 to 5 tables. I was going to do 1 to 10, but the code block is already kind of long. If more than 5 are needed, would be easy enough to modify. Posting it again here in case anyone is interested in a more flexible version.. just another way to get there.. might be useful for someone who wants an easy UDF and does not mind saving as .xlsm. Thanks for the inspiration to tinker and learn :)) Thumbs up!! Function AppendOneToFiveTables(rng1 As Range, Optional rng2 As Range, Optional rng3 As Range, Optional rng4 As Range, Optional rng5 As Range) Dim r1 As Long, r2 As Long, r3 As Long, r4 As Long, r5 As Long, c As Long r1 = rng1.Rows.Count If Not rng2 Is Nothing Then r2 = rng2.Rows.Count If Not rng3 Is Nothing Then r3 = rng3.Rows.Count If Not rng4 Is Nothing Then r4 = rng4.Rows.Count If Not rng5 Is Nothing Then r5 = rng5.Rows.Count c = rng1.Columns.Count Dim arr As Variant ReDim arr(1 To r1 + r2 + r3 + r4 + r5, 1 To c) Dim i As Long, j As Long For i = 1 To r1 + r2 + r3 + r4 + r5 For j = 1 To c If i
@@excelisfun Thanks Mike.. but more like beginner's luck.. haha :)) I have some VBA needs in my work.. things that can't be done with formulas.. so.. been trying to learn as much as I can. There is so much depth and breadth to EXCEL, sometimes I feel like I'm just scratching the surface. But, it's a fun journey. Thanks for all your support. ExcelIsFun is #1!! Go Team :))
@@wayneedmondson1065 We all just scratch the surface. That is why it is so much fun. It never ends. We all know very little, but have much deliciousness to come. Whatever task we may be given, we will find a way with Excel. Go Team! Go Infinity : )
Hi Mike, I've been watching your videos regularly. Learnt a lot & implemented them in my work, it's been awesome. I have been challenged by a requirement at work: How can we append additional data (in a table), to the same source table using power query and load it back to the source table? Please create a video on the same. Presume I've not confused you!!😃
Very easy to follow steps, great presentation. Thanks for sharing, I am trying to combine multiple worksheets, each work sheets having different header columns. For eg, Sheet 1 has A,B,C has columns. Sheet 2 has B,D,E has columns, Sheet 3 has A,CF has columns, Sheet 4 has E,G,H has columns, I wanted to merge all 4 sheets together A,B,C,D,E,F,G,H is this possible in Power Query? Can you please share a technique if there is any?
@@excelisfun I would create a new attribute for you as you train me and most probably also others in a such entertaining style, you will be as of now my *XL-ENTERTRAINER*.
The names have to be the same to do a direct append. Otherwise I think you have to use M Code to remove all field names and replace with a consistent set of field names.
As usual - another great and informative video. On another subject - I heard Liam Hendricks was visiting the Jays spring training site the other week. Maybe the beginning of a future signing for my Jays? I think Hendricks has priced himself out of the A's budget. If he leaves, who would be your new closer?
Wondering if you can help. Searched every set of keywords I can think of, but coming up empty. Here’s situation: I have a query that reaches out to a website, loads data from table on the site, and adds a custom column with date and time of query on each row, then loads it to a table on a worksheet. What I want to do is each time I refresh the query, I want it to append the refresh day’s data to the end of the table that resulted from prior refreshes. Thanks.
Hi I have a question about automating updating the source tables in excel power query I mean if I add or remove columns in the source table how to updating it in power query Thanks
Hi, Thank you for your excel videos I learnd and learning from you a lot, In my office we dealing with an excal tables that are very large, and if combined will exceed the excel limitation (~1,040,000). Is there a way to use power query to craete a csv / txt file from other sheets/csv tables without the limitations of excel sheet ?
It was informative:) Mike could you please tell us how to combine tables dynamically? How to combine tables using passing parameters ? suppose there are 4 tables A,B,C,D and I hav eto combine only 2 table B&C using parameter.
Thanks! I would like to use 3 top rows as my headers. How do I do that? 2nd and 3rd row comes after every table. Can you tell me how do I fix this? Thanks
Hi, Professor, you have provide in a lot of effort for these videos, thank you, I have a question if we have for example in cell "a1" the number 10.00 m, how to have this number with the same format in cell "b1 "using a text function or some other function, thank you very much.😃👍
Good morning, when we use the search functions in Excel, we do not get the cell formatting that we are looking for. I want to get the full cell format. Is that possible? I thank you for all the efforts you are making through these videos, thank you very much.😃👍
@@mohamedadjal8502 What do you mean by search? The Find feature? The SEARCH function? The Find feature can look for formatting if you click options. SEARCH function can not look for format.
@@excelisfun Good evening Professor, I thank you for the answers that you sent me on the Internet. May God protect you. Suppose we have in cell a1 = "exel", in cell a2 = "is", in cell a3 = "fun", in cell b1 = 12.00m, in cell b2 = 10.00gr, in cell b3 = 15.00kg, use the vlookup function: vlookup = ("is", $ a $ 1: $ b $ 3,2, false), the result is 10, which means that This function didn't give me the full format of the number in cell b2 (b2 = 10.00gr), my goal is to get b2 = 10.00gr and not b2 = 10. thank you so much.😃👍
Mike @3:25: why didn't you just add Grades3 to the list of arguments in the Source Step?
Makes for a more elegant solution IMO.
I was trying to NOT do M Code in this one because my target was beginners. But for us, it is MUCH easier to to do exactly what you say: just quickly amend M Code Table.Combine : )
Thanks, for the hot tip : ) : ) : ) : )
O, but wait... I did make the beginners type out =Excel.CurrentWorkbook() ... lol
@@excelisfun Aha, exactly: you DID start off with a “typie” (and that is not a typo). ;-)
And frankly, I find doing a little typie (read as “tie-pee”) where you amend the formula by continuing the pattern, quite intuitive.
You may even sell it as “cheating” a little bit. Some people like that attitude. I just call it: elegant. [crosses arms, looks into the distance and pauses for effect]
Now, don’t get me wrong: I love the PQ-M interface to pieces, but we Exceleers [shameless plug] are used to the formula bar, so why not play with it once in awhile. :-)
Oh man, how far have we sunk, I mean: come, I said: come. I mean that we now take this playing for granted.
It’s friday - humor me. :-)
@@GeertDelmulle You are right about selling it as cheatings. I must have 500 examples of saying exactly that lol BUT: what I really should say from now on is now we're gunna cheat here, urr, I mean, make it elegant lol^2 And you are right, we Exceleers ARE used to formula, so why not ; )
Okay, since it's Friday: here is a joke to humor you: wait, I am bad with jokes and don't know any. Well, in lieu of that, we will just bask in the rad glory of being able to play and have fun!!!! Go Team!
@@excelisfun Mike, you know: the other day one of my more hairy and muscular colleagues was making fun of me on a conf.call, and said that all that Excel is all well and good, but, he asked -especially now in the lockdown- if I was doing any lifting? [paused for effect]
In contrast to what he expected, my response was quick and enthousiastic:
Me: Oh, yeah, sure, ever since February 2020 I’m used to lifting. All. The. Time. I even do Pairwise Lifting. It’s the first thing I do in the morning. Especially because of Corona. [paused for effect, myself]
He: Really?!? OK, hey, I didn’t know that...
And we left it at that.
He had NO IDEA. He didn’t get the joke,... but I’m sure YOU do.
.
.
.
.
PS: Yes, this was a joke - I don’t have any hairy, muscular colleagues (that I know of) and that conversation never happened.
You know I can’t lie. :-)
King of PQ has spoken!! No more words!!😀✌✌
Come on now, Awesome Teammate cr gr0912... The King of PQ M Code is Bill Szysz!!!!!!!!!!!!! lol
Exactly mate he's the king 👌
@@MehmetAliMD Thanks, Cutting_Edge_Tech - just trying to make the world a better place one video at a time : )
Thank you from Thailand Mike :)
You are welcome, Wan Lazy Bear!!!!
Boom!3 Awesome Methods Using The Mighty Power Query...Thank You Mike :)
Power "BOOM" Query is Mighty. That is the perfect word for it: Mighty : ) : )
Thanks for this video. It was helpful.
You are welcome!!!
You have returned to complete another trilogy with the Power Query part.
Excellent all the work to append tables.
Thank you very much Mike.
You are welcome for the trio of fun, Ivan : ) : ) : )
Seeing your amazing videos is fun
Glad it is fun for you, Updates Guru : )
Very handy tutorial
Big thanks
Glad you like it!!!
Thanks a lot it was helpful.
You are welcome, Abhinav!!!!
Thanks for unselfishly sharing your knowledge, you are changing people's lives in a better way!
You are welcome : ) That has been goal here at UA-cam since 2008 : )
You are the Master of excel , thank you very much
Thanks!
Thanks for the donation, Tr Street!!!
Awesome as always... Love the new intro Mike...
Glad you like the new intro. Our Awesome Teammate GEERT made it : ) What a Team we have!!!!!!!!!!!!!!!
I can tell there is something great in this video, I miss your videos
How can you tell ; )
If you miss them, The Reform, you can always boomerang back to this excelisfun channel for more fun : ) : )
I don't know method two until watch this video. Thank you for sharing.
Glad you found method 2 useful, Shih-Chi!!!!
Thanks mike brother ... You are simply best .. we are expecting more videos on m code used in PQ
I am so consumed by my job during covid 19 and with extra family duties, while trying to finish my book (which is 6 months behind), but in about a year after I finish my book and the associated videos, I either plan to write a book about Power Query M Code or concentrate on videos about that. The real problem is that life keeps getting in the way of all the Excel fun that I want to do... : (
Amazing work. Thanks a lot Mike
You are welcome!
Thank you Mike i love excel because of you :). I also watch your amazing video that cover pivot table is there any video that explain all aggregate operations that appear in the pivot table menu
Yes, of course : ) Here it is: ua-cam.com/video/e-yuYNgsHAk/v-deo.html
@@excelisfun Thank you appreciated
Great as usual Mike
Glad it is good for you, abo!!!!
All 3 are methods are nice but I will go with 1st one.
Yes, sir!!!!!! #1 is good : )
Thanks Mike. i enjoyed that although tge formula videos were outstanding.
Yes, for a Formula Guy (like you and I), this is fun, but...
Great video Mike, and the intro is great too!
Glad you like it. The intro came from our Teammate Geert :) : ) : ) : ) : )
Thanks Mike. This is a fantastic channel Nd community. I learn something everytime I stop by.
You are welcome, Matt : )
Great video! I had over 2m rows to merge into one pivot and this did the trick :)
Glad this could help!
Great vid! Love the new intro bumper vid!
Thanks, Teammate, Doug H!!!!!
Hi Mike. Awesome lesson.. as always! Power Query is such a powerful and useful feature to know. Thanks for the examples. In keeping with this theme, I upgraded my UDF to allow for appending from 1 to 5 tables.. had a friend who liked the first version, but wanted to have the option to use it on something other than exactly 3 tables. Back to the drawing board and a small adjustment gives the user the option of appending from 1 to 5 tables. I was going to do 1 to 10, but the code block is already kind of long. If more than 5 are needed, would be easy enough to modify. Posting it again here in case anyone is interested in a more flexible version.. just another way to get there.. might be useful for someone who wants an easy UDF and does not mind saving as .xlsm. Thanks for the inspiration to tinker and learn :)) Thumbs up!!
Function AppendOneToFiveTables(rng1 As Range, Optional rng2 As Range, Optional rng3 As Range, Optional rng4 As Range, Optional rng5 As Range)
Dim r1 As Long, r2 As Long, r3 As Long, r4 As Long, r5 As Long, c As Long
r1 = rng1.Rows.Count
If Not rng2 Is Nothing Then r2 = rng2.Rows.Count
If Not rng3 Is Nothing Then r3 = rng3.Rows.Count
If Not rng4 Is Nothing Then r4 = rng4.Rows.Count
If Not rng5 Is Nothing Then r5 = rng5.Rows.Count
c = rng1.Columns.Count
Dim arr As Variant
ReDim arr(1 To r1 + r2 + r3 + r4 + r5, 1 To c)
Dim i As Long, j As Long
For i = 1 To r1 + r2 + r3 + r4 + r5
For j = 1 To c
If i
Awesome, Wayne!!! You are such a smart VBA guy : ) : ) : ) : )
Thanks for the post, Excel Teammate : ) : )
@@excelisfun Thanks Mike.. but more like beginner's luck.. haha :)) I have some VBA needs in my work.. things that can't be done with formulas.. so.. been trying to learn as much as I can. There is so much depth and breadth to EXCEL, sometimes I feel like I'm just scratching the surface. But, it's a fun journey. Thanks for all your support. ExcelIsFun is #1!! Go Team :))
@@wayneedmondson1065 We all just scratch the surface. That is why it is so much fun. It never ends. We all know very little, but have much deliciousness to come. Whatever task we may be given, we will find a way with Excel. Go Team! Go Infinity : )
@@excelisfun Go Magic Mike!! Go Team :))
Second method is amazing 👍
Nice and informative video
Useful. Thank you Mike.
You are welcome, Luciano : ) : ) : )
Hi Mike please Post a video on appending changing Header column in power query
Top tier Excel content as always, thanks!
You are welcome!
Hi Mike, I've been watching your videos regularly.
Learnt a lot & implemented them in my work, it's been awesome.
I have been challenged by a requirement at work: How can we append additional data (in a table), to the same source table using power query and load it back to the source table?
Please create a video on the same.
Presume I've not confused you!!😃
nice video！！！
Amazing! Thank you, Mike!
Glad you liked it, Teammate : )
I found method one easy and quick
Indeed, it is quite useful. Quick and easy is always useful : ) : )
Ur name has been changed as Marvellous Mike!!!
Dear Ashish,
Thank you : )
Sincerely, Marvellous Mike
Wow. Mike, that's amazing. Thanks for the share.
You are welcome for the share, fellow teacher : ) : ) : )
how log tym mike but your welcm 👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻💯
Glad to have you back, Vijay : ) : ) : )
the best
Very easy to follow steps, great presentation. Thanks for sharing, I am trying to combine multiple worksheets, each work sheets having different header columns. For eg,
Sheet 1 has A,B,C has columns.
Sheet 2 has B,D,E has columns,
Sheet 3 has A,CF has columns,
Sheet 4 has E,G,H has columns,
I wanted to merge all 4 sheets together A,B,C,D,E,F,G,H is this possible in Power Query? Can you please share a technique if there is any?
So nice to have a new video from u mike sir and very pleasent for ears to hear your voice, were have been you sir missed your videos so much😈😈😈😈
Glad that you like to hear and see the videos, HARISH!!!!
As Always, very entertaining lessons. 🚀
Stay safe in 2021 too.
Glad it is edifyingly entertaining, Roger! Here is to a better 2021!!!
@@excelisfun I would create a new attribute for you as you train me and most probably also others in a such entertaining style, you will be as of now my *XL-ENTERTRAINER*.
@@RogerStocker LOVE it!!!! *XL-ENTERTRAINER* We gotta get this new term into dictionary: entertrainer ; )
Amazing video! Any suggestions for append data when we have different columns names ?
The names have to be the same to do a direct append. Otherwise I think you have to use M Code to remove all field names and replace with a consistent set of field names.
We need to find a way to get a training receipt from watching these so we can use them to get RU credits for recertification.
Riiiiight. That would be great : )
As usual - another great and informative video. On another subject - I heard Liam Hendricks was visiting the Jays spring training site the other week. Maybe the beginning of a future signing for my Jays? I think Hendricks has priced himself out of the A's budget. If he leaves, who would be your new closer?
Power Query! 😀👍💯💥
Power Query is so mighty : ) : )
Wondering if you can help. Searched every set of keywords I can think of, but coming up empty. Here’s situation: I have a query that reaches out to a website, loads data from table on the site, and adds a custom column with date and time of query on each row, then loads it to a table on a worksheet. What I want to do is each time I refresh the query, I want it to append the refresh day’s data to the end of the table that resulted from prior refreshes. Thanks.
Awesome! Thank you, MIke :)
You are welcome, MVP Malina : )
Hi
I have a question about automating updating the source tables in excel power query
I mean if I add or remove columns in the source table how to updating it in power query
Thanks
Can you still append two or more tables if the number and names of columns in each table are different
In the first example, How would you get the two tables to append horizontally instead of vertically?
Hi,
Thank you for your excel videos I learnd and learning from you a lot,
In my office we dealing with an excal tables that are very large, and if combined will exceed the excel limitation (~1,040,000).
Is there a way to use power query to craete a csv / txt file from other sheets/csv tables without the limitations of excel sheet ?
grrrreeeaaattt! thank you!! :)
You are welcome, Long Time Teammate : )
All sheets in workbook have to in table form PL cofirm
@ExcelIsFun @5:34, How should i get the list to show all code like you show at that time
It was informative:) Mike could you please tell us how to combine tables dynamically? How to combine tables using passing parameters ? suppose there are 4 tables A,B,C,D and I hav eto combine only 2 table B&C using parameter.
Thanks! I would like to use 3 top rows as my headers. How do I do that? 2nd and 3rd row comes after every table. Can you tell me how do I fix this? Thanks
I really wish that an easier way to append arrays available in excel, similar to fastexcel's append udf.
Thank you so much sir , Could you please make video on how to fetch NSE Stock data from NSE Site with power Query ?
I do not have a subscription to this site: www.nyse.com/market-data . Do you?
@@excelisfun
NSE india sir ,
How to fetch website table using power query ?
Hi, Professor, you have provide in a lot of effort for these videos, thank you, I have a question if we have for example in cell "a1" the number 10.00 m, how to have this number with the same format in cell "b1 "using a text function or some other function, thank you very much.😃👍
Maybe: =TEXT(A1,"0.00")&" m"
Good morning, when we use the search functions in Excel, we do not get the
cell formatting that we are looking for.
I want to get the full cell format. Is that possible? I thank you for all the efforts you are making through these videos,
thank you very much.😃👍
@@mohamedadjal8502 What do you mean by search? The Find feature? The SEARCH function? The Find feature can look for formatting if you click options. SEARCH function can not look for format.
@@excelisfun Good evening Professor, I thank you for the answers that you sent me on the Internet. May God protect you. Suppose we have in cell a1 = "exel", in cell a2 = "is", in cell a3 = "fun", in cell b1 = 12.00m, in cell b2 = 10.00gr, in cell b3 = 15.00kg, use the vlookup function: vlookup = ("is", $ a $ 1: $ b $ 3,2, false), the result is 10, which means that This function didn't give me the full format of the number in cell b2 (b2 = 10.00gr), my goal is to get b2 = 10.00gr and not b2 = 10. thank you so much.😃👍
Mike, how do I keep appending to the file. i.e I have a master file but I want to keep adding a new file every week
Method 3 in this video.
How to prevent datasets loading itself repeatedly?
Filter out query name. I showed this at 06:17
Filter out query name. I showed this at 06:17
how to contact you sir, from India