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!!! 🙏🙏👍👍🔥🔥🔥
@@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
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
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 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!
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.....
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.
From all the RUclipsrs 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
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.
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...
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 😘
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!
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.
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”
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!!
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
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
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!
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!
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 RUclips as a small channel? It would be useful.... And Great Video 🔥🔥🔥
Leila, love so much your video.. thanks a lot, those help me so much. but how if more than 2 workers in the same depart and project if I use first method? thank you Leila...
This is a great video, but my question is how do we turn it back from table number two, into table number one? I am currently cleaning dataset where it has too many columns and too many rows where it shows blank and "1". For example, sarah is a marketing person, hence the row marketing and column named sarah is marked "1", while the rest of the columns are blank
Hi, nice channel. I learned a lot thanks. Btw, could you please explain power query for Mac. I haven’t managed to get it for Mac. I will really love to use it.
Love your videos. I have a question is there a way to make xlookup dynamic when looking up from a pivot table, that is constantly being updated as currently I’m having to change range each time. I don’t know if you have a video regarding this problem. 🤔🤗
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
Super solution. Was stuck at work with one file for a long time. And got it resolved with this solution .... just like magic
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
You are life saver. I have looking for this solution for the past three days. Thank you so much
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
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
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!
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.
Wow, nice tutorial. No need for special paid classes. YT is best teacher. And for excel Leila is best.
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 RUclips channel (for me of course..) and feel very excited to see it grow..
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.....
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!
Thanks!
Thank you!
I got a job just by learning from your videos. Thank you, may happiness comes with you
That's great to hear!
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
Leila you are the best! You encourage me to learn VBA, DAX and and Power Query!
From all the RUclipsrs 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
Loved this, though the DAX version was difficult! Formula and PQ rock! Thank you Leila for another great tutorial! 😊👍
Thanks Vijay.
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.
I learned a couple things from this video. Thank you.
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...
Magic, you are reaching new levels now .. solving a problem in different ways .. all new to me 🤪.. i feel dumb
Thanks
Thank you!
I actually miss your Excel videos , as usual great teachings
you are my savior! thanks for this so much helping video, hope more people discover you.
You're very welcome!
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 😘
Spectacular demonstration of knowledge of the subject. Thank you Leila!
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!
Great example and comparison of 3 different methods to achieve the result
Thank you! Cheers!
You always Teach very easily miss.
Appreciate it
Great video with great instruction Miss Leila.
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.
what can I say, you are very good at explaining, you teach me many beautiful tricks. congratulations Leila 👍
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”
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!!
Leila Gharani the great! Massive love from Nigeria.
Leila's Tshirt: "Excel can do it! Now what's the question?" 🤣🤣
You have definitely taught me that! Thank you Ms.Leila 🙏🏾
Random Guy: I'm good at Excel.
Leila: Okay, please hold my glass!
Congratulations on approaching one million subscribers. You are awesome and deserve the best. Thank you for all the expert information you provide.
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
Always best Teacher
lots of love from India
4th way: add to data model in pivot table, add concatenatex in dax measure, remove grand total for rows and columns in design tab. That's all
Thank you for the method3, using DAX. That's very important!
My pleasure, Iván.
Thanks for the additional formula functions introduced here, got to rewatch and study it
Coming soon 1M🥳🥳🥳🥳 congratulations in advance
Leila, you totally rock! Happy weekend otherwise
As always Leila, you're amazing! I learned something new again today from you! Really awesome! ❤
Excellent tutorial! Many thanks for sharing Leila
Thank you for the tutorial. I'd go with the Formulas approach.
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
the first two solution is great, third one was overkill :)
Omg, thank you so much! You saved my life.
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.
Leila another great video. For me formulas are hands down in this case
Great video! I vote for Power Query.
Excellent video Leila-- I am learning DAX currently and your explanantion was really concise. Would love to see more DAX videos from you!!
I like the PQ version better. Thanks for an awesome video as always "You Rock".
Thanks for watching, Robert!
Thank you so much mam to give us too knowledge 🙏🏻🙏🏻😊
Thanks for explaining it "Simply".....
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!
i really love the Dax approach
Thank a lot for the lesson 👍👍👍🙋🙏
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!
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.
You are Amazing! 👌
I like your all videos helped me alot
Excellent tutorial. Power Pivot looks much effective. Though I am familiar with PQ, found this method little difficult
Congratulations! for 1 Million and Thanks for these very impressive and useful tutorials. Sharing knowledge is the best job ❤️
Very interactive and intelligent way👌🕸
I really like this series. A quick question. How can I add the UNIQUE function to Excel 2016?
that's amazing. thank you very much.
It's really helped me...tq
Great explanation
thank you!
Dear Marisa
You can also visit our channel to learn ms Excel and macros for free.
ruclips.net/channel/UC8WvuiN2SfVA8p6Uhx8UmpQ
So helpful, thanks Leila!
Thank you Chris!
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?
Dang, I ❤️ you! Truly awesome! Thank you for this!
My pleasure!
Thank you very much
for grand total hide - simply go to design >grand total>off rows and columns
Thanks for sharing
Nice Video thanks
Hey Leila Congratulations on 1M subscribers
Thank you!
Beautiful and tempting
Hi Leila, when is Dax course coming out eagerly waiting for it to learn more about it
Outstanding. Thank you
My pleasure!
I am looking for this type of tutorial.Cute
Super Amazing!
Bravooo 👏👏👏👏
I have been noticing that your subscribers are growing very fast!! Could you please make a video on how to grow on RUclips as a small channel? It would be useful.... And Great Video 🔥🔥🔥
Leila, love so much your video.. thanks a lot, those help me so much. but how if more than 2 workers in the same depart and project if I use first method? thank you Leila...
Good job
That's great!!!! how can I transfer (textjoin/filter) into VBA? so update the table anytime it's needed
Wow... Great learning
Wouah, amazing !!!
Thank you
This is a great video, but my question is how do we turn it back from table number two, into table number one? I am currently cleaning dataset where it has too many columns and too many rows where it shows blank and "1". For example, sarah is a marketing person, hence the row marketing and column named sarah is marked "1", while the rest of the columns are blank
Amazing :)
Hi, nice channel. I learned a lot thanks. Btw, could you please explain power query for Mac. I haven’t managed to get it for Mac. I will really love to use it.
Love your videos. I have a question is there a way to make xlookup dynamic when looking up from a pivot table, that is constantly being updated as currently I’m having to change range each time. I don’t know if you have a video regarding this problem. 🤔🤗
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.