I really like your videos where you explain every possible question people ask! Your answer everything, so smart!! No wonder you won the MVP prize in Microsoft!!! Your are the best, i have seen your website too!!! 🙏🙏👍👍🔥🔥🔥
I like the formula method best, as it doesn't require the user to right-click & refresh to update the report. Those new formulas that spill values are very cool.
Thanks Andrew! Yes - planning something different.... I'll post on the community tab later this week. Thank you so much for all your support, comments & likes!
Man, I been looking for formulas for ages to do this. Never knew about power queries either. Gonna have to rewatch this video multiple times. Shame I can't thumbs up for each method.
The truth is brilliant, your videos are incredible with the practicality you explain, I use each of the things you show in the day-to-day life of my profession!!!!!!!
@@LeilaGharani I challenge you with something. It works magic as we are used to with cube formulas. But be careful, I give you a challenge...... use some variant with mdx to, for example, filter the members of the cube that only present records.....
Using the combination of Unique, Transpose and Filter functions has helped me transform a very large data set which would have taken me hours to format. I very much appreciate this video so thank you for posting this. Have been an avid watcher of your channel but I am very much a novice with Excel but love it. Thanks again Ian
@@LeilaGharani Legend. I was wondering if you could make a video for someone who has to deal with a new datasets. Basically the approaches he/she has to take to build up the meaningful reports. For instance, someone who recently hired as a demand planner in a company and wants to impress the managers with figures and facts the person can takeout from row data. I also have another idea (to make video) you maybe interested. Thanks again
This is really amazing, especially the CONCATENATEX part.. I have applied these formula and other tricks to my data and it worked. Leila you're just on the next level's next level...
I used to think Excel was for nerds until I stumbled across Lella. Now I am captivated and signing up for a course with her. Who would have thought! There should be mug with the encryption 'Excel with Lella.' She is enchanting.
From all the UA-camrs I subscribed... You are the one from whom I learned a lot and continue this journey along with you.. Lot of positives things besides the subject matter makes your video very interesting and interactives... Lot of well wishes for you and thumbs up for your efforts... Keep rocking
Eager to see a full course on DAX next. I have learnt various skills from a lot of courses and video lectures, but the confidence and level of comfort in their use I was able to build were only once I went through yours. Truly unmatchable!
In the PQ approach you can also use a dummy criteria in the pivot aggregation (for example count) and then with the query editor hack the pivot line substituting the “count” with “text.combine”
Recently, i got assign to make dynamic table and i was struggling since 3days and atlast your video helped me alot and how to make a dynamic table👏👏🏆I wanna say Big Thank you!!!!for showing us a dynamic table best example😊😊@Leila
Hi Leila, Pivot the table by adding below step after the Changed Type in applied steps: Table.Pivot( #”Changed Type” , List.Distinct(#”Changed Type”[Department]) , “Department” , “Project” , (Person) => Text.Combine( Person , “#(lf)” ) ) I LOVE Power Query 😘
Hey Leila, you can add this video to your PowerQuery & Advance Excel courses as update. Just my thought since I've subscribed both in Udemy. Thanks for this wonderful video.
I really could have used this 2 days ago. I've been trying to dev a cause and effect diagram for instrumentation I/O and this is way easier than using an access database. Thank you!
You can fix a table reference when dragging or copying, you just need to double up your column reference and surround with extra square brackets e.g. TProject[[Person]:[Person]]. Don’t forget to separate the two instances with a colon.
Wow! I knew the first method - but I learned a lot about the other 2 methods, especially the DAX/Power Pivot method! Is DAX the same as Excel formulas - if not, could you please continue doing videos on DAX? I am very fascinated by it! Also, I didn't know how to use Power Query to create and extract a list of values! Very interesting!
Hi, Thank you very much for sharing all these guides and tips... I wonder is there any way to move multiple cells together from one column to another please
Hi Leila. An awesome lesson! My first pass on the FILTER function solution was: =FILTER(TProject[Person],(TProject[Project]=$F2)*(TProject[Department]=G$1),""). I like your use of "&" to concatenate the two conditions of the include argument.. very nice! PQ and DAX solutions are great too. Thanks for sharing these useful techniques and solutions :)) Thumbs up!!
Thanks for the video. It really helps. I have a question though. I have table of data which has unique data for different cases. like in your video, there are three variables / columns but in my case instead of third column I have 6 more column of data. Now I need to switch my rows into columns keeping the 6 columns as they repeat for each row with unique values. I am not sure, if I am able to explain my problem or not but hoping to hear from you.
I find the Power Query and Dax options actually the easiest to understand and to get my head around. The Formula option was next level. Do you need to go all the way to suppress the totals or will it not be easier to just switch the grand totals off on the Pivot Design?
I have been noticing that your subscribers are growing very fast!! Could you please make a video on how to grow on UA-cam as a small channel? It would be useful.... And Great Video 🔥🔥🔥
Grab the file I used in the video from here 👉 pages.xelplus.com/transform-table-file
Also, just curious if anyone recognized these project names?
Yes, they are all sports and I think from Olympics... Correct??
They appear to be James Bond references. I recognised Grand Slam from Goldfinger and Thunderball is, obviously, from Thunderball.
Abcdefg
Expect a Dax course from you
RC Communication
I really like your videos where you explain every possible question people ask! Your answer everything, so smart!! No wonder you won the MVP prize in Microsoft!!! Your are the best, i have seen your website too!!! 🙏🙏👍👍🔥🔥🔥
So nice of you! Thank you.
BTW, Your website is amazing
Super solution. Was stuck at work with one file for a long time. And got it resolved with this solution .... just like magic
You are life saver. I have looking for this solution for the past three days. Thank you so much
I like the formula method best, as it doesn't require the user to right-click & refresh to update the report. Those new formulas that spill values are very cool.
Power query is better when you are working on huge data and specially form different sources
Almost at the magic million mark now. Hope there’s a special video lined up!
Thanks Andrew! Yes - planning something different.... I'll post on the community tab later this week. Thank you so much for all your support, comments & likes!
Expecting a big one here
Same here.. Never been this excited before following a very useful UA-cam channel (for me of course..) and feel very excited to see it grow..
Thanks Leila. I have multiple use cases for each of these three methods. Thanks for sharing them in one concise video.
Thank you Matt! Glad you find it useful.
I learned a couple things from this video. Thank you.
Man, I been looking for formulas for ages to do this. Never knew about power queries either.
Gonna have to rewatch this video multiple times. Shame I can't thumbs up for each method.
Thanks for watching!
The truth is brilliant, your videos are incredible with the practicality you explain, I use each of the things you show in the day-to-day life of my profession!!!!!!!
Wow, thank you!
@@LeilaGharani I challenge you with something. It works magic as we are used to with cube formulas. But be careful, I give you a challenge...... use some variant with mdx to, for example, filter the members of the cube that only present records.....
Using the combination of Unique, Transpose and Filter functions has helped me transform a very large data set which would have taken me hours to format. I very much appreciate this video so thank you for posting this. Have been an avid watcher of your channel but I am very much a novice with Excel but love it. Thanks again Ian
Wow, it’s amazing Leila… query method looks like the easiest way. I had to rewire my brain 🧠 after I watched the DAX version! Too hard for me 🔥🧠🔥
Thanks Afshin! Agree with you about the DAX part. I wasn't going to add it, but someone asked about it and I couldn't resist 😊
@@LeilaGharani Legend.
I was wondering if you could make a video for someone who has to deal with a new datasets. Basically the approaches he/she has to take to build up the meaningful reports.
For instance, someone who recently hired as a demand planner in a company and wants to impress the managers with figures and facts the person can takeout from row data.
I also have another idea (to make video) you maybe interested.
Thanks again
Thank you!! I've been struggle for so long, and finally found this video! Your explanation is really easy to understand. Thank you again :)
Glad it was helpful!
I actually miss your Excel videos , as usual great teachings
Loved this, though the DAX version was difficult! Formula and PQ rock! Thank you Leila for another great tutorial! 😊👍
Thanks Vijay.
Great example and comparison of 3 different methods to achieve the result
Thank you! Cheers!
Leila you are the best! You encourage me to learn VBA, DAX and and Power Query!
Wow, nice tutorial. No need for special paid classes. YT is best teacher. And for excel Leila is best.
i really love the Dax approach
This is really amazing, especially the CONCATENATEX part.. I have applied these formula and other tricks to my data and it worked. Leila you're just on the next level's next level...
Random Guy: I'm good at Excel.
Leila: Okay, please hold my glass!
Thanks for the additional formula functions introduced here, got to rewatch and study it
You always Teach very easily miss.
Appreciate it
Always best Teacher
lots of love from India
Omg, thank you so much! You saved my life.
you are my savior! thanks for this so much helping video, hope more people discover you.
You're very welcome!
Leila Gharani the great! Massive love from Nigeria.
I used to think Excel was for nerds until I stumbled across Lella. Now I am captivated and signing up for a course with her. Who would have thought! There should be mug with the encryption 'Excel with Lella.' She is enchanting.
From all the UA-camrs I subscribed... You are the one from whom I learned a lot and continue this journey along with you.. Lot of positives things besides the subject matter makes your video very interesting and interactives... Lot of well wishes for you and thumbs up for your efforts... Keep rocking
Eager to see a full course on DAX next. I have learnt various skills from a lot of courses and video lectures, but the confidence and level of comfort in their use I was able to build were only once I went through yours. Truly unmatchable!
Spectacular demonstration of knowledge of the subject. Thank you Leila!
Thank you for the method3, using DAX. That's very important!
My pleasure, Iván.
In the PQ approach you can also use a dummy criteria in the pivot aggregation (for example count) and then with the query editor hack the pivot line substituting the “count” with “text.combine”
Magic, you are reaching new levels now .. solving a problem in different ways .. all new to me 🤪.. i feel dumb
Great video with great instruction Miss Leila.
Thanks for explaining it "Simply".....
Thank you for the tutorial. I'd go with the Formulas approach.
Leila, you totally rock! Happy weekend otherwise
what can I say, you are very good at explaining, you teach me many beautiful tricks. congratulations Leila 👍
Leila's Tshirt: "Excel can do it! Now what's the question?" 🤣🤣
You have definitely taught me that! Thank you Ms.Leila 🙏🏾
Great video! I vote for Power Query.
the first two solution is great, third one was overkill :)
Excellent tutorial! Many thanks for sharing Leila
Recently, i got assign to make dynamic table and i was struggling since 3days and atlast your video helped me alot and how to make a dynamic table👏👏🏆I wanna say Big Thank you!!!!for showing us a dynamic table best example😊😊@Leila
Hi Leila,
Pivot the table by adding below step after the Changed Type in applied steps:
Table.Pivot( #”Changed Type” , List.Distinct(#”Changed Type”[Department]) , “Department” , “Project” , (Person) => Text.Combine( Person , “#(lf)” ) )
I LOVE Power Query 😘
Excellent tutorial. Power Pivot looks much effective. Though I am familiar with PQ, found this method little difficult
Hey Leila, you can add this video to your PowerQuery & Advance Excel courses as update. Just my thought since I've subscribed both in Udemy. Thanks for this wonderful video.
I got a job just by learning from your videos. Thank you, may happiness comes with you
That's great to hear!
Leila another great video. For me formulas are hands down in this case
As always Leila, you're amazing! I learned something new again today from you! Really awesome! ❤
Thank a lot for the lesson 👍👍👍🙋🙏
Coming soon 1M🥳🥳🥳🥳 congratulations in advance
I like the PQ version better. Thanks for an awesome video as always "You Rock".
Thanks for watching, Robert!
thank you!
Dear Marisa
You can also visit our channel to learn ms Excel and macros for free.
ua-cam.com/channels/8WvuiN2SfVA8p6Uhx8UmpQ.html
Great explanation
Beautiful and tempting
So helpful, thanks Leila!
Thank you Chris!
Very interactive and intelligent way👌🕸
Congratulations on approaching one million subscribers. You are awesome and deserve the best. Thank you for all the expert information you provide.
I really like this series. A quick question. How can I add the UNIQUE function to Excel 2016?
Thank you so much mam to give us too knowledge 🙏🏻🙏🏻😊
that's amazing. thank you very much.
I really could have used this 2 days ago. I've been trying to dev a cause and effect diagram for instrumentation I/O and this is way easier than using an access database.
Thank you!
I like your all videos helped me alot
You can fix a table reference when dragging or copying, you just need to double up your column reference and surround with extra square brackets e.g. TProject[[Person]:[Person]]. Don’t forget to separate the two instances with a colon.
Excellent video Leila-- I am learning DAX currently and your explanantion was really concise. Would love to see more DAX videos from you!!
Thank you very much
Thanks for sharing
It's really helped me...tq
You are Amazing! 👌
Wow! I knew the first method - but I learned a lot about the other 2 methods, especially the DAX/Power Pivot method!
Is DAX the same as Excel formulas - if not, could you please continue doing videos on DAX? I am very fascinated by it!
Also, I didn't know how to use Power Query to create and extract a list of values! Very interesting!
Outstanding. Thank you
My pleasure!
Hi, Thank you very much for sharing all these guides and tips... I wonder is there any way to move multiple cells together from one column to another please
Dang, I ❤️ you! Truly awesome! Thank you for this!
My pleasure!
Nice Video thanks
Hi Leila. An awesome lesson! My first pass on the FILTER function solution was: =FILTER(TProject[Person],(TProject[Project]=$F2)*(TProject[Department]=G$1),""). I like your use of "&" to concatenate the two conditions of the include argument.. very nice! PQ and DAX solutions are great too. Thanks for sharing these useful techniques and solutions :)) Thumbs up!!
Wow... Great learning
Thank you
Super Amazing!
Thanks!
Thank you!
I am looking for this type of tutorial.Cute
Thanks for the video. It really helps. I have a question though. I have table of data which has unique data for different cases. like in your video, there are three variables / columns but in my case instead of third column I have 6 more column of data. Now I need to switch my rows into columns keeping the 6 columns as they repeat for each row with unique values. I am not sure, if I am able to explain my problem or not but hoping to hear from you.
Good job
Congratulations! for 1 Million and Thanks for these very impressive and useful tutorials. Sharing knowledge is the best job ❤️
I find the Power Query and Dax options actually the easiest to understand and to get my head around. The Formula option was next level.
Do you need to go all the way to suppress the totals or will it not be easier to just switch the grand totals off on the Pivot Design?
Wouah, amazing !!!
I have been noticing that your subscribers are growing very fast!! Could you please make a video on how to grow on UA-cam as a small channel? It would be useful.... And Great Video 🔥🔥🔥
Hey Leila Congratulations on 1M subscribers
Thank you!
Amazing!!
Simply wow
Amazing :)
Thanks
Thank you!
Tq so much
Hi Leila, when is Dax course coming out eagerly waiting for it to learn more about it
The DAX way is amazing
Hi Leila, this is all good stuff but like any solution I would always like to know which is the quickest method when dealing with volumes.
Bravooo 👏👏👏👏
for grand total hide - simply go to design >grand total>off rows and columns
Looks like the power query one was the cleanest and least complicated. Would be nice to get your opinion on
the drawbacks of each.
Amazing t-shirt phrase 😅