Can't tell you how much this helps! I was going insane trying to process a .csv with a one-off, staggered column issue for days. Before this, I was manually editing the file in Excel, then saving, then loading into Power Query. The problem comes when you have dozens of files you have to do this to. Now I can just do it in one step from the Query! Big ups for helping us neophytes out, man!
Love it! Thank you for being original. This is an awesome tool to have. Unfortunately, it didn't work for my data... It worked just as you described, but my data is not completely filled in, or in other words, it had a lot of blank rows, and I needed to move them all up a row. So, everything filled the way you said, but I'd have to tweak it a bit maybe, because it filled in all the blank rows, which needed to stay blank in order to maintain the data. But I loved the video and I still learned stuff and things!
Thank you very much for your content. I have been struggling with some transformations and this video gave me the inspiration to solve them. I love your style!! You are amazing!!
Thanks again for sharing these tips, I love the thought process that has gone on to solve that issue. On a separate note one thing I noticed the other day is this tip that can save a step.... When selecting the final columns you want.,actually click on them in the order you want them to appear and then select remove other columns. No need to re-order the columns afterwards. Keep the Funk Up.
I tried another thing: added two index columns, one starting from 0, the other from 1. Then I merged Table1 with himself: the "first Table1" using the 0 indexed column and the "second Table1" using the 1 indexed one, and the default Left outer join. Maybe not as funky as Oz's but it saved 5 steps!
Great video! Great use of the modulo function. Here's one question, can this work horizontally? I have some horrid text file source data, delimited only by spaces, and something like this seems it could help in getting each row into a normalized column.
PowerQuery does its work vertically. When you need something to work horizontally, it sometimes helps to prepare your data for Transpose, then do what you need to do, then transpose the data back to its original orientation.
Thanks, I would never have thought about this :-) Video idea: What if some of the cells are in the wrong column. E.g. if there are multiple commas/seperators in a text. I think, I would just replace the wrong comma manually in the text before csvsplit if it could be identified and replace it back afterwords. The problem might be the same with text seperators like doublequote.
You've got me curious. I'd like to see an example of what you're describing. Last week I dealt with something that had 3 spaces separating text. th-cam.com/video/12BNPs6XQgE/w-d-xo.html Is this something similar to what you're describing?
The problem is that you receive a csv (comma seperated) file from somebody like a big customer or vendor who will not change the export because of some errors. It is your problem. ProdId,ProdDescription 1,Funky, Function, Friday 2,Power Query DataRow1: 4 colums DataRom2: 2 columns I have seen it so often that there is an extra comma, tab, quote or semicolon in the productdescription. Dependent on your format names like O'Kelly can make problems if ' is text seperator. The same is the case if you have a 19" display monitor. You video had the same format for all rows as far as I remember. My case is when only a few rows has a strange format because the exporter used a separator contained in a text value.
Gotcha! That's when you can choose the delimiter. For things like: ID, Name, Age H3, Marty Jones, 51 H2, Candace Marshall, PhD, 38 H7, Iris McDaniels, CPA, CFP, 36 If you split by every comma, you'd get a mess. At 4:59 of this video you see an option to split "at the leftmost delimiter" th-cam.com/video/12BNPs6XQgE/w-d-xo.htmlm52s That's were you can do more careful parsing. In the example I gave here, you'd split the ID (leftmost delimiter) and then split out the age (rightmost delimiter).
Thanks :-) I was not aware of right-most. Now you can attack a column from both sides unless you have a sequence of messed columns. Left-most and Right-most is missing in the official documentation: support.office.com/en-us/article/Split-a-column-of-text-Power-Query-5282d425-6dd0-46ca-95bf-8e0da9539662
I hadn't realized that about the MSFT documentation. One thing that's both a plus and a minus about MSFT going to the software-as-service model is that things change fast. Theyr'e constantly adding, fixing and updating stuff. So, it's possible that they're making changes faster than they can update their documentation. I've noticed that they've even changed the look of icons. I post a video one day and 2 weeks later something no longer looks like it did in the video. It can be frustrating.
When the query has a rhyme
It saves me so much time
Thank you EOF.
Incredible!
You're so welcome JB!
Your video helped me in my MBA course. I am cleaning data... the struggle has been real. Thank you so much!!
BEAUTIFUL! I'm so glad this helped. And yes, the struggle is real.
I always have a fun time and learn a lot when I watch your videos. Thanks for sharing the good educational vibes.
Can't tell you how much this helps! I was going insane trying to process a .csv with a one-off, staggered column issue for days. Before this, I was manually editing the file in Excel, then saving, then loading into Power Query. The problem comes when you have dozens of files you have to do this to. Now I can just do it in one step from the Query! Big ups for helping us neophytes out, man!
Loved the solution, Your Style and the music.
Thank You
YAAYYYY! Thank you so much. :)
Really loved the video, this really helped me out when trying to tidy up some data extracted from JIRA.
This has to be the most enjoyable excel video I've watched, and I've learned something on top of that. Thank you.
You are so welcome, and thanks for letting me know you appreciated this video. 😊
Could you provide the name of the song in this vid?
I'm out of town. When I get back home I'll look at the raw files and let you know the name of the song. 👍
Still waiting on the song, haha.
@@XxparrotBobxX Me too, name of the song!
Love it! Thank you for being original. This is an awesome tool to have. Unfortunately, it didn't work for my data... It worked just as you described, but my data is not completely filled in, or in other words, it had a lot of blank rows, and I needed to move them all up a row. So, everything filled the way you said, but I'd have to tweak it a bit maybe, because it filled in all the blank rows, which needed to stay blank in order to maintain the data. But I loved the video and I still learned stuff and things!
Thank you very much for your content. I have been struggling with some transformations and this video gave me the inspiration to solve them. I love your style!! You are amazing!!
There is absolutely no way you can forget this lesson. A lot of cool action happening....
😀 thank you!
This is exactly what I was looking for! and it put a great big smile on my face at the same time. Brilliant!
Thank you so much! YAAAYYYY! I'm glad you got what you needed and had fun. 🤩
Thanks again for sharing these tips, I love the thought process that has gone on to solve that issue. On a separate note one thing I noticed the other day is this tip that can save a step.... When selecting the final columns you want.,actually click on them in the order you want them to appear and then select remove other columns. No need to re-order the columns afterwards. Keep the Funk Up.
I tried another thing: added two index columns, one starting from 0, the other from 1. Then I merged Table1 with himself: the "first Table1" using the 0 indexed column and the "second Table1" using the 1 indexed one, and the default Left outer join. Maybe not as funky as Oz's but it saved 5 steps!
Any tips if the cell(s) you need to shift is a row, not a whole column?
So fun to watch and learn!! Thanks a lot!!!
PUJA! Thank you. 🙏
This technique is awesome! Thank you so much!
You are so welcome!
Great video! Great use of the modulo function. Here's one question, can this work horizontally? I have some horrid text file source data, delimited only by spaces, and something like this seems it could help in getting each row into a normalized column.
PowerQuery does its work vertically. When you need something to work horizontally, it sometimes helps to prepare your data for Transpose, then do what you need to do, then transpose the data back to its original orientation.
"Insert a MODULO"
FUNKY MUSIC STARTS PLAYING
There ya go! Gotta get that funky MODULO!
Love the beat
HELL YEAH!
I can't stop watching these videos! Learning Excel has never been funkier. #ohhhyeah
Thanks so much! 😀
Do you use Excel?
Nope. I'm a designer and I pretty much hated using Excel... but I can't stop watching these Excel videos! And I'm learning a ton, also!
Beautiful! I'm glad you're enjoying these. And you're always welcome to ask questions. 🤗
thanks for sharing ... super duper solution....
it's another Funky Function Friday! Thanks Oz!
You are very welcome
Freaking awesomeness! Thank you for your help! 👏👏👏🙏🙏🙏
You're very welcome
Thank you Oz one way to master powerquery is practicing these examples even if they are weird sometimes :-)
Really helpful!!! Thanks a lot!!
Thanks, I would never have thought about this :-)
Video idea: What if some of the cells are in the wrong column. E.g. if there are multiple commas/seperators in a text. I think, I would just replace the wrong comma manually in the text before csvsplit if it could be identified and replace it back afterwords. The problem might be the same with text seperators like doublequote.
You've got me curious. I'd like to see an example of what you're describing.
Last week I dealt with something that had 3 spaces separating text.
th-cam.com/video/12BNPs6XQgE/w-d-xo.html
Is this something similar to what you're describing?
The problem is that you receive a csv (comma seperated) file from somebody like a big customer or vendor who will not change the export because of some errors. It is your problem.
ProdId,ProdDescription
1,Funky, Function, Friday
2,Power Query
DataRow1: 4 colums
DataRom2: 2 columns
I have seen it so often that there is an extra comma, tab, quote or semicolon in the productdescription.
Dependent on your format names like O'Kelly can make problems if ' is text seperator. The same is the case if you have a 19" display monitor.
You video had the same format for all rows as far as I remember. My case is when only a few rows has a strange format because the exporter used a separator contained in a text value.
Gotcha! That's when you can choose the delimiter.
For things like:
ID, Name, Age
H3, Marty Jones, 51
H2, Candace Marshall, PhD, 38
H7, Iris McDaniels, CPA, CFP, 36
If you split by every comma, you'd get a mess.
At 4:59 of this video you see an option to split "at the leftmost delimiter"
th-cam.com/video/12BNPs6XQgE/w-d-xo.htmlm52s
That's were you can do more careful parsing. In the example I gave here, you'd split the ID (leftmost delimiter) and then split out the age (rightmost delimiter).
Thanks :-)
I was not aware of right-most. Now you can attack a column from both sides unless you have a sequence of messed columns.
Left-most and Right-most is missing in the official documentation:
support.office.com/en-us/article/Split-a-column-of-text-Power-Query-5282d425-6dd0-46ca-95bf-8e0da9539662
I hadn't realized that about the MSFT documentation.
One thing that's both a plus and a minus about MSFT going to the software-as-service model is that things change fast. Theyr'e constantly adding, fixing and updating stuff. So, it's possible that they're making changes faster than they can update their documentation.
I've noticed that they've even changed the look of icons. I post a video one day and 2 weeks later something no longer looks like it did in the video. It can be frustrating.
Great Trick! thank you
The file very useful bcos some of us learn by doing Thank You
You're welcome.
I remembered to upload it this time. :)
Like fine wine, get better w/ age😊
You could learn this stuff somewhere else, but then you'd miss out on the culture and magic.