it is always a pleasure and very rich on learning, to watch your videos! fantastic how you explain what you are doing and why you do it! many thanks!!!
Superb, you should do more power query videos! Thank you, Gasper! Just to mention that fill up/down works only if the cells are truly blank. Sometimes, when excel returns an empty string for example from if statement (""), this is not a null cell, but it's displayed without values in PQ. In those cases, we need first to replace empty string values with null. I feel that PowerQuery is a must nowadays. The other day I've had to merge 40 excel files with a different structure. It takes me some time to search how it could be done, but in the end, it was like 4 clicks dynamic solution. PQ is such a powerful tool! You can make a video for combining some excel data with the same/different structure. I believe that it'll be an interesting one!
Exactly Stefan. But it turns out people are afraid of what they don't know 🤔. Imagine that. That's why I created this video, to get those people in on things they do know 😀
Hi Gasper. Totally agree! Power Query is awesome for so many things. Thanks for demonstrating. Also, just for fun, if you want another way to extract numbers from the right side of a variable length text string, here is a formula that will work: =--RIGHT(SUBSTITUTE(E4," ",REPT(" ",LEN(E4))),LEN(E4)). Thanks and thumbs up!!
for fill up fill down, I found a simple solution, first put table under filter then only keep blank rows visible, then go to first empty row example this cell is A3 then go A3 and put formula =A2 and drag this formula or select till bottom and Alt+; and Ctl+D, i learnt Alt+; from you thanks for that very usefull
Cool trick Dhaval. The only problem is if that was really a table, then a formula definition could overwrite the entire column or it could not automatically copy in the future so it's a risk. Thanks for the contribution Dhaval. This is how we grow.
@@ExcelOlympics ohh I meant the data is in tabular form not use the "Table" feature, that would make selection putting a fitler easy, your obsertavtion is correct if we use the "Table" feature then yes we have to be carefull it doesnt simply overwrite everything
Dear Gašper, thanks for the great content. I don't fully understand which function triggers the array to be spilled? Point 11:45, the =MID(E4;ROW(INDIRECT("1:"&LEN(E4)));1) formula. I really appreciate any help you can provide.
It's the ROW function which goes something like =ROW(1:12) which gives you a "column" or rather a list of 1,2,3,4... which spills in new excel. Way back this was an Array formula where you needed Ctrl+Shift+Enter...
it is always a pleasure and very rich on learning, to watch your videos! fantastic how you explain what you are doing and why you do it! many thanks!!!
Gasper, truly awesome ideas showing us just how brilliant Power Query is to deal with situations in a repeatable manner! Thank you.
My pleasure Al.
Great Video Gasper, The split into rows is just so useful!!
True Martin. Split to rows is a lifesaver when you need it.
And I'm glad you liked the video.
Superb, you should do more power query videos! Thank you, Gasper!
Just to mention that fill up/down works only if the cells are truly blank. Sometimes, when excel returns an empty string for example from if statement (""), this is not a null cell, but it's displayed without values in PQ. In those cases, we need first to replace empty string values with null.
I feel that PowerQuery is a must nowadays.
The other day I've had to merge 40 excel files with a different structure. It takes me some time to search how it could be done, but in the end, it was like 4 clicks dynamic solution. PQ is such a powerful tool! You can make a video for combining some excel data with the same/different structure. I believe that it'll be an interesting one!
Exactly Stefan. But it turns out people are afraid of what they don't know 🤔. Imagine that.
That's why I created this video, to get those people in on things they do know 😀
I'm 100% agree! The first step is usually the hardest.
True.
Hi Gasper. Totally agree! Power Query is awesome for so many things. Thanks for demonstrating. Also, just for fun, if you want another way to extract numbers from the right side of a variable length text string, here is a formula that will work: =--RIGHT(SUBSTITUTE(E4," ",REPT(" ",LEN(E4))),LEN(E4)). Thanks and thumbs up!!
Thanks Wayne. Power Query to everyone! 😀
for fill up fill down, I found a simple solution, first put table under filter then only keep blank rows visible, then go to first empty row example this cell is A3 then go A3 and put formula =A2 and drag this formula or select till bottom and Alt+; and Ctl+D, i learnt Alt+; from you thanks for that very usefull
Cool trick Dhaval. The only problem is if that was really a table, then a formula definition could overwrite the entire column or it could not automatically copy in the future so it's a risk.
Thanks for the contribution Dhaval. This is how we grow.
@@ExcelOlympics ohh I meant the data is in tabular form not use the "Table" feature, that would make selection putting a fitler easy, your obsertavtion is correct if we use the "Table" feature then yes we have to be carefull it doesnt simply overwrite everything
Exactly Dhaval. Cool trick though 😎
Great video!
Thank you Kenneth.
Thank you Gašper. Excellent as always
Thank you for those kind words Davor. Much appreciated.
Dear Gašper, thanks for the great content. I don't fully understand which function triggers the array to be spilled?
Point 11:45, the =MID(E4;ROW(INDIRECT("1:"&LEN(E4)));1) formula. I really appreciate any help you can provide.
It's the ROW function which goes something like =ROW(1:12) which gives you a "column" or rather a list of 1,2,3,4... which spills in new excel. Way back this was an Array formula where you needed Ctrl+Shift+Enter...