Это видео недоступно.
Сожалеем об этом.
Convert Multiple Column Groups to Rows in Power Query
HTML-код
- Опубликовано: 7 авг 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, we'll explore the solution from the last Power Query Challenge!
➜ Download My Solution + Other solutions in the blog comments
goodly.co.in/convert-multiple...
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== CHAPTERS =====
0:00 Intro
0:19 Revisiting the Problem
0:58 Problems with Transpose Operations
1:43 Formative Understanding of the Solution
2:43 Extracting the Names of the Column
4:18 Converting All the columns in 'List' format
6:48 Creating Pair of Two
10:22 Make the Pair Count Dynamic & Get Locations
12:53 Combine Locations and Column Pairs
15:50 Shoutout & My Courses
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!
- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ
Official After The Fall RUclips Channel Below
ruclips.net/channel/UCGQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses
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 - ruclips.net/video/TtBDA34Hhf0/видео.html
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
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 :)
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.
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
Genius indeed! Fantastic. Brilliant! I'm in awe!
Amazing, I have this exact problem. You are a great trainer, you explained a difficult solution easy to follow
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?
This is so visual and well explained; amazing!
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 RUclipsrs 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:)
Amazing! It becomes an art, an entertainment! Thanks for that and waiting for another release! :-)
Glad you liked it!
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
This is really good, Goodly! Thank you for sharing!
Glad you like it !
Great explanation with working session for this complex data set. Thanks you !!
Glad you like it !
Cleared many basic concepts
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!
I also have this same problem. Thank you this creative solution.
Incredible! Thanks, I am really getting a better understanding of how M code works.
Great to hear!
Incredible video it's helpful for my project.. only one challenge in my dataset is date in single column
Glad it helped!
Wonderful Chandeep- you make it look too easy!
Thanks for watching!
Exquisite. Simply exquisite. Bravo sir. Thank you for sharing!
Glad you like it !
another fantastic video, chandeep. not just teaching us pq. also teaching us to think like programmers.
Thank you!
Awesome ... as usual Chandeep !
It was very complicated for me, but you made it so simple.Thanks Bro..
Glad it was helpful!
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!
Incredible! Thanks, Thanks for your wonderful video
Glad you enjoyed it!
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!
Brilliantly explained. Cheers
Glad you liked it!
I like your approach. Keep up the good work!
Thanks Manuel!
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 !
Спасибо, оч круто ) А то я всегда пользовался транспонированием
THAT was cool! Thanks!
Glad you liked it!
Unbelievable 👏👏 learned a lot of functions that can be used variably in other aspects too 🎉😊
Glad you liked it !
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.
Excelente saludos desde Guatemala!!!
Thank you!
I will try with my data, hopefully it will be automate my work. THANKS A MILLION PAAJI ❤️
Always welcome
You are incredible
Super amazing 😍
Unbelievably super dynamic...
Thanks!
You are absolutely crazy genius
Thanks !
Fantastic Paji
You are rock
Thanks !
Thank you for creating this video Exact solution to what i was looking 4.........Cheerzzz
You're very welcome!
Great video!!
Thank you !
very Great !!!
Thank you!
You are the boss!!!
Thank You !
You have a indepth knowledge of DAX and it's become tricky for people like us who are still learning. 😁😁😁😁
This reminds me of functional programming in a Lisp, like Clojure.
Awesome trick.
Thanks!
Superb!!!!
Thank you!
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, what's your idea for this one?
very good
Hi, these steps would be considered as query folded?
amazing solution🙂
Thanks!
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
Greatest 🎉🎉
Thank you !
This is dam good!!!
Glad you like it !
Magic 🎉❤
Thank You !
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
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
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.
Please check the alternative solution of using Pivot and unpivot method without breaking streaming of power query
Link to video - ruclips.net/video/TtBDA34Hhf0/видео.html
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.
Nice
Thanks!
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?
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.
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.
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
Now that's some good M :)
Thank You !
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))
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
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 RUclips soon :)
This can also be solved using the native Excel array formulae...
Hello Sir
Please make a power bi complete tutorial from beginner to advance level in Hindi
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.
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
this guy is god of M
Glad you think so!
🤩
Thanks !
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
You look like a magician who does belive in Impress others rather than teaching
Thanks Raj :)
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 ;)
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.
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 🤣🤣