I've been searching for 2 days trying to find a solution to this kind of messy raw data with this specific format. I've gotten unbelievably lucky for you to upload this right when I was looking for it! thank you so much!
Your way of handling M to flip and twist the data without actually doing the steps is very inspiring and on a whole different level than many other UA-camrs who show step by step code. Keep up the wonderful work of spreading knowledge and understanding! Thank you very much!
I am working on the same problem on my project where I have 9 columns and I need to keep 3 columns and stack the data underneath of 3 columns. This solution will work perfect for me but I need to watch this video for many times to understand better. You're providing solutions to many PQ users! Bravo :)
unbelievable. I am new in PQ, just starting to learn it about a week ago by self-thaugt. At the 1st time, i guess this is a course for advance users, but with your excellent explanation, it's easy to understand by everyone. Now i learn many things from your channel everyday. thank you
I KNEW there would be more to it than the Transform-Pivot-Unpivot approach! 😮 I really need to learn this "not breaking the stream" thing... I look forward to your video on that soon! Thank you, Chandeep!! 👍
Fantastic as usual, one comment, if we add sub column to only one location the query won't work precisely. i did a solution using a combination of transpose group by and pivot (I know it is not as clean as yours) but it caters for this probability. at the end, i can't thank you enough for the sharing your valuable ideas
I had a table with some common columns then 19 groups of 9 columns of which I wanted to use 5 columns from each group. In the end I made a union query in Access. I worry that I might have errors in that query. Now I see that powerQuery could do the job. But there is a learning curve. Thank you for showing me that what I want to do should be possible. Question: How to be sure that data is correctly transformed?
I shall be working through this quite a few times I think, I always seem to learn a new function , if not more, on watching your videos. Thank you. Oh, as someone else has commented "not breaking the stream' , don't understand?
Wow! This is so powerful. As Martin Kuek says, there's understanding the separate functions, but you also need to know how to combine them in the stepwise-driven process that is Power Query. So far, my PQ experience is with each step referring to the previous one. But here, you're referring back to earlier steps. I came across this video via a Google search, and I think I'll be able to adapt your techniques into what I need to do. Many thanks!
Hi bro, thanks for your effort today I learned one more new thing. Do you have any solution to automatically track ranges in various excel sheet in PQ. As I need to append them and every time i have to convert data in table form.
i followed your video to create however, in my case i have col.1 item col.2 price1 col.3 qty1 col.4 price2 col.5 qty2 and list of additional prices and qty's cols. i wanted to append all the price and qty for the same list of items. Any suggestions?
I'm trying to do this with some longitudinal data. The initial column has meaning and I want to retain it and merge it with each set of 2 values. SourceDataHeaders: SomeIDThatHasMeaning,Name1,Age1,Name2,Age2,Name3,Age3,.....,Name40,Age40 EndDataHeaders: SomeIDThatHasMeaning,Name,Age I was hoping I could remerge the list (one list for all the IDs and another with my split sets of 2) but haven't figured that out yet.
This solution, namely using List.Split, assumes a consistent number of sub-headers for each city. If any city had a different number of sub-headers than the rest, this would fail. I considered using List.Split, but I thought it violated the conditions of the challenge as its not truly dynamic. Garucia had an excellent solution that was fully dynamic.
What if I have another column How can I bring them into the column pairs Example I have a column named index It contains numbers 1-6 So using your solution i will just take everything other than what it is colA Then I want to make sure col A is added into the col pairs of name and age How do I put that?
Let's say you have an additional column at the begining of the table: The name of the column would be CompanyName and in this column from row 2 to 7 you would have the name of company. How would add this colmun at the end of your model? thanks
Hello Chandeep, I am stuck at 1:49 . Can you please explain the line Source{0}[Data]. When I am loading the data, power query is converting it into excel and then loading.
Basic question, but when you start by extracting the columns names, you have an M Query line Sheet1_Sheet{1}. What is Sheet1_Sheet, because I get a message saying that Sheet1_Sheet isn't recognised ? I've tried the table name_Sheet and that didn't work
Dear Charan, Need a solution for extracting the data from a table which has values in the below columns: Column_A, Column_B, Column_C, Column_X, Value X, Column_X, Value_Y, Column_X, Value_Z In such as way that it looks like Column_A, Column_B, Column_C, Column_X, Value_X Column_A, Column_B, Column_C, Column_X, Value_Y Column_A, Column_B, Column_C, Column_X, Value_Z Which means that here the Column_X has been repeated 3 time (in actual report this is repeated 30 times) and after each Column_X, value of that is placed in the next column against the same (Value_X, Value_Y, Value_Z ...... again in 30 columns). Look forward to a solution.
your videos are really helping me a lot in M. I am stuck in power query if you could please help me. I am not able to write dax or command excel line in M if you could please help me =VALUE(MID([DevData],FIND("CT",[DevData])+3,FIND(";",MID([DevData],FIND("CT",[DevData]),LEN([DevData])))-4))
Knowing what all those functions do is one thing. But your creativity to put them all together into a solution is next level genius.
Thank you!
I've been searching for 2 days trying to find a solution to this kind of messy raw data with this specific format. I've gotten unbelievably lucky for you to upload this right when I was looking for it! thank you so much!
Cool! Glad it was helpful
Checkout this alternative solution as well - ua-cam.com/video/TtBDA34Hhf0/v-deo.html
Your way of handling M to flip and twist the data without actually doing the steps is very inspiring and on a whole different level than many other UA-camrs who show step by step code. Keep up the wonderful work of spreading knowledge and understanding! Thank you very much!
Thanks Tommy.. I should expect to see you around more often:)
I am working on the same problem on my project where I have 9 columns and I need to keep 3 columns and stack the data underneath of 3 columns.
This solution will work perfect for me but I need to watch this video for many times to understand better.
You're providing solutions to many PQ users! Bravo :)
unbelievable. I am new in PQ, just starting to learn it about a week ago by self-thaugt. At the 1st time, i guess this is a course for advance users, but with your excellent explanation, it's easy to understand by everyone. Now i learn many things from your channel everyday. thank you
Incredible! I have always disliked the transpose/pivot/unpivot approach for anything but the smallest data sets. This is a much better solution.
Thanks! Glad you like it
I KNEW there would be more to it than the Transform-Pivot-Unpivot approach! 😮 I really need to learn this "not breaking the stream" thing... I look forward to your video on that soon! Thank you, Chandeep!! 👍
Wow ! What a way to do it without involving transpose anywhere.
You have the source of all the DAX formulas and used it in right place at right time
ज़बरदस्त 👏🏽
क्या learning हुई . . मज़ा आ गया 👌🏽
Thank You 🙏🏽
Friend I don't know how many times they have told you; but you are a genius...greetings from Chile
Thank you for your nice words !
Fantastic as usual, one comment, if we add sub column to only one location the query won't work precisely. i did a solution using a combination of transpose group by and pivot (I know it is not as clean as yours) but it caters for this probability. at the end, i can't thank you enough for the sharing your valuable ideas
Very interesting. I achieved solution with pivot, fill down, unpivot 😅. I short lot of steps I created. thank you for this easy solution.🎉
Glad it helped!
I had a table with some common columns then 19 groups of 9 columns of which I wanted to use 5 columns from each group. In the end I made a union query in Access. I worry that I might have errors in that query.
Now I see that powerQuery could do the job. But there is a learning curve. Thank you for showing me that what I want to do should be possible.
Question: How to be sure that data is correctly transformed?
omg this is wht i'v been searching for a whole week
I shall be working through this quite a few times I think, I always seem to learn a new function , if not more, on watching your videos. Thank you. Oh, as someone else has commented "not breaking the stream' , don't understand?
Incredible video it's helpful for my project.. only one challenge in my dataset is date in single column
Glad it helped!
Just found this channel and it's so amezing how much I can learn from it. Thanks for your hard work, please continue sharing your videos.
It was very complicated for me, but you made it so simple.Thanks Bro..
Glad it was helpful!
You have a indepth knowledge of DAX and it's become tricky for people like us who are still learning. 😁😁😁😁
I will try with my data, hopefully it will be automate my work. THANKS A MILLION PAAJI ❤️
Always welcome
Amazing, I have this exact problem. You are a great trainer, you explained a difficult solution easy to follow
Incredible! Thanks, I am really getting a better understanding of how M code works.
Great to hear!
Wow! This is so powerful. As Martin Kuek says, there's understanding the separate functions, but you also need to know how to combine them in the stepwise-driven process that is Power Query. So far, my PQ experience is with each step referring to the previous one. But here, you're referring back to earlier steps. I came across this video via a Google search, and I think I'll be able to adapt your techniques into what I need to do. Many thanks!
Amazing! It becomes an art, an entertainment! Thanks for that and waiting for another release! :-)
Glad you liked it!
Very helpful solution, and I think fairly elegant. I learned some useful techniques here that apply in lots of other situations too. Thanks!
Glad it was helpful!
Genius indeed! Fantastic. Brilliant! I'm in awe!
Hi, what's your idea for this one?
Cleared many basic concepts
Hi I wish I could remember all the M code you use. I just love your solutions and approach to problems. Thanks!
Glad you like them!
I also have this same problem. Thank you this creative solution.
Hi, these steps would be considered as query folded?
Fantastic. But what's happening how can your method improves if only Mumbai has a plus sub-column, so not all cities have same amount
Exquisite. Simply exquisite. Bravo sir. Thank you for sharing!
Glad you like it !
This is so visual and well explained; amazing!
Unbelievably super dynamic...
Thanks!
Hi bro, thanks for your effort today I learned one more new thing. Do you have any solution to automatically track ranges in various excel sheet in PQ. As I need to append them and every time i have to convert data in table form.
i followed your video to create
however, in my case i have
col.1 item
col.2 price1
col.3 qty1
col.4 price2
col.5 qty2
and list of additional prices and qty's cols.
i wanted to append all the price and qty for the same list of items.
Any suggestions?
Wonderful Chandeep- you make it look too easy!
Thanks for watching!
I'm trying to do this with some longitudinal data. The initial column has meaning and I want to retain it and merge it with each set of 2 values.
SourceDataHeaders: SomeIDThatHasMeaning,Name1,Age1,Name2,Age2,Name3,Age3,.....,Name40,Age40
EndDataHeaders: SomeIDThatHasMeaning,Name,Age
I was hoping I could remerge the list (one list for all the IDs and another with my split sets of 2) but haven't figured that out yet.
This solution, namely using List.Split, assumes a consistent number of sub-headers for each city. If any city had a different number of sub-headers than the rest, this would fail. I considered using List.Split, but I thought it violated the conditions of the challenge as its not truly dynamic. Garucia had an excellent solution that was fully dynamic.
Which channel you referring to?
Yes. I agree. Her code is fantastic!
See the blog comments - goodly.co.in/convert-multiple-column-groups-to-rows-power-query/
I cold not find Garcinia channel. Can you send me the lik, pls
@@adlaalnajmi9162 See the blog comments as Goodly linked in this thread
How do you expand the lists into one long column but in the order of groups of two? List.combine?
so good and really nicely explained! you won another subscriber!
You are absolutely crazy genius
Thanks !
Please check the alternative solution of using Pivot and unpivot method without breaking streaming of power query
Link to video - ua-cam.com/video/TtBDA34Hhf0/v-deo.html
Thank you for creating this video Exact solution to what i was looking 4.........Cheerzzz
You're very welcome!
What if I have another column
How can I bring them into the column pairs
Example
I have a column named index
It contains numbers 1-6
So using your solution i will just take everything other than what it is colA
Then I want to make sure col A is added into the col pairs of name and age
How do I put that?
Let's say you have an additional column at the begining of the table: The name of the column would be CompanyName and in this column from row 2 to 7 you would have the name of company. How would add this colmun at the end of your model? thanks
another fantastic video, chandeep. not just teaching us pq. also teaching us to think like programmers.
Thank you!
How do keep only columns I need/remove other columns based on a List?
How do I create that List in Power Query or import that List?
Thanks
Excelente saludos desde Guatemala!!!
Thank you!
Great explanation with working session for this complex data set. Thanks you !!
Glad you like it !
Unbelievable 👏👏 learned a lot of functions that can be used variably in other aspects too 🎉😊
Glad you liked it !
I like your approach. Keep up the good work!
Thanks Manuel!
Hello Chandeep, I am stuck at 1:49 . Can you please explain the line Source{0}[Data]. When I am loading the data, power query is converting it into excel and then loading.
Basic question, but when you start by extracting the columns names, you have an M Query line Sheet1_Sheet{1}. What is Sheet1_Sheet, because I get a message saying that Sheet1_Sheet isn't recognised ? I've tried the table name_Sheet and that didn't work
Brilliantly explained. Cheers
Glad you liked it!
Спасибо, оч круто ) А то я всегда пользовался транспонированием
Awesome ... as usual Chandeep !
Dear Charan,
Need a solution for extracting the data from a table which has values in the below columns:
Column_A, Column_B, Column_C, Column_X, Value X, Column_X, Value_Y, Column_X, Value_Z
In such as way that it looks like
Column_A, Column_B, Column_C, Column_X, Value_X
Column_A, Column_B, Column_C, Column_X, Value_Y
Column_A, Column_B, Column_C, Column_X, Value_Z
Which means that here the Column_X has been repeated 3 time (in actual report this is repeated 30 times) and after each Column_X, value of that is placed in the next column against the same (Value_X, Value_Y, Value_Z ...... again in 30 columns).
Look forward to a solution.
Everything is correct but I have 1 more column which I want to accumulate into each column group is that possible? Someone please help me
hi, i think that solution is very hard! why you do not go with transpose and then merge the row headers and after that use unpivot other columns.
Hello Sir
Please make a power bi complete tutorial from beginner to advance level in Hindi
This is really good, Goodly! Thank you for sharing!
Glad you like it !
Incredible! Thanks, Thanks for your wonderful video
Glad you enjoyed it!
This reminds me of functional programming in a Lisp, like Clojure.
Absolute genius! Any courses for beginners?
See this - goodly.co.in/learn-power-query/
@@GoodlyChandeep does this include M? I have a good understanding and use of PQ, need to learn M from scratch and the way you explain it is brilliant.
@@ExcelWithChris I am currently working on a course on M Language.. You'll hear the announcement on UA-cam soon :)
Fantastic Paji
You are rock
Thanks !
This can also be solved using the native Excel array formulae...
your videos are really helping me a lot in M. I am stuck in power query if you could please help me. I am not able to write dax or command excel line in M if you could please help me
=VALUE(MID([DevData],FIND("CT",[DevData])+3,FIND(";",MID([DevData],FIND("CT",[DevData]),LEN([DevData])))-4))
That's a lot of helpp, especially to someone like who is just starting with M.
Glad it was helpful !
@@GoodlyChandeep Hey! It is. A lot of thanks from the Philippines. I just started a Reporting job so this is really helpful.
THAT was cool! Thanks!
Glad you liked it!
I don't see the name "Sheet1_Sheet" on your list of applied steps, so I don't understand how you are able to refer to it.
the navigation step is called Sheet1_Sheet
@@GoodlyChandeep Thanks, I guess I missed how you give a step a custom name without changing the displayed name.
@@bagnon Well you cannot. That unique anomaly is only limited to the Navigation Step ;)
You are incredible
amazing solution🙂
Thanks!
Awesome trick.
Thanks!
Super amazing 😍
Great video!!
Thank you !
You look like a magician who does belive in Impress others rather than teaching
Thanks Raj :)
Thanks!
Thank you so much :)
You are the boss!!!
Thank You !
Superb!!!!
Thank you!
very Great !!!
Thank you!
this guy is god of M
Glad you think so!
very good
Greatest 🎉🎉
Thank you !
Now that's some good M :)
Thank You !
Why is the First step Sheet_Sheet1 instead of Navigation?
Haha.. Navigation is reserved name. You cannot use it.
Just hop over to the advanced editor to see the actual name of the navigation step.
This is dam good!!!
Glad you like it !
Magic 🎉❤
Thank You !
Nice
Thanks!
Please teach .lam 14 and would like to be an amazing DJ just like marshmallow
Great video, but not all of us are fellas
Friends feels too clichéd. I am treating "fellas" as gender agnostic 😜
why do i bother? it's out of my league 🤣🤣
🤩
Thanks !