@@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
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.
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.
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!
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!!! 🙏🙏👍👍🔥🔥🔥
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.
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...
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!
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 😘
From all the TH-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
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”
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.....
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!!
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
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.
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.
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.
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
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 have been noticing that your subscribers are growing very fast!! Could you please make a video on how to grow on TH-cam as a small channel? It would be useful.... And Great Video 🔥🔥🔥
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?
If you want your structured references to be absolute you can use this syntax TableName[[Column1]:[Column1]] In case of this video it would be TProject[[Project]:[Project]]
Hey Leila, instead of having the data concatenated in same cell, is it possible to have it displayed in multiple rows. Is there a solution to achieve this result?
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...
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
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
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
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.
Random Guy: I'm good at Excel.
Leila: Okay, please hold my glass!
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 TH-cam channel (for me of course..) and feel very excited to see it grow..
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
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!
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...
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!
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.
Loved this, though the DAX version was difficult! Formula and PQ rock! Thank you Leila for another great tutorial! 😊👍
Thanks Vijay.
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 😘
Wow, nice tutorial. No need for special paid classes. YT is best teacher. And for excel Leila is best.
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!
From all the TH-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
Leila you are the best! You encourage me to learn VBA, DAX and and Power Query!
Thanks!
Thank you!
I learned a couple things from this video. Thank you.
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”
I actually miss your Excel videos , as usual great teachings
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
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.....
Magic, you are reaching new levels now .. solving a problem in different ways .. all new to me 🤪.. i feel dumb
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
Thank you!
you are my savior! thanks for this so much helping video, hope more people discover you.
You're very welcome!
I got a job just by learning from your videos. Thank you, may happiness comes with you
That's great to hear!
Spectacular demonstration of knowledge of the subject. Thank you 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
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.
Always best Teacher
lots of love from India
Thanks for the additional formula functions introduced here, got to rewatch and study it
thank you!
Dear Marisa
You can also visit our channel to learn ms Excel and macros for free.
th-cam.com/channels/8WvuiN2SfVA8p6Uhx8UmpQ.html
Leila's Tshirt: "Excel can do it! Now what's the question?" 🤣🤣
You have definitely taught me that! Thank you Ms.Leila 🙏🏾
Leila Gharani the great! Massive love from Nigeria.
As always Leila, you're amazing! I learned something new again today from you! Really awesome! ❤
Great example and comparison of 3 different methods to achieve the result
Thank you! Cheers!
Thank you for the method3, using DAX. That's very important!
My pleasure, Iván.
the first two solution is great, third one was overkill :)
Great video with great instruction Miss Leila.
You always Teach very easily miss.
Appreciate it
Coming soon 1M🥳🥳🥳🥳 congratulations in advance
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.
Omg, thank you so much! You saved my life.
Leila, you totally rock! Happy weekend otherwise
Thank you so much mam to give us too knowledge 🙏🏻🙏🏻😊
what can I say, you are very good at explaining, you teach me many beautiful tricks. congratulations Leila 👍
Thank a lot for the lesson 👍👍👍🙋🙏
Great video! I vote for Power Query.
Congratulations on approaching one million subscribers. You are awesome and deserve the best. Thank you for all the expert information you provide.
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 for the tutorial. I'd go with the Formulas approach.
i really love the Dax approach
Excellent tutorial! Many thanks for sharing Leila
I like the PQ version better. Thanks for an awesome video as always "You Rock".
Thanks for watching, Robert!
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!
Leila another great video. For me formulas are hands down in this case
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.
Thanks for explaining it "Simply".....
You are Amazing! 👌
Great explanation
Very interactive and intelligent way👌🕸
Dang, I ❤️ you! Truly awesome! Thank you for this!
My pleasure!
I like your all videos helped me alot
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
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!
Excellent tutorial. Power Pivot looks much effective. Though I am familiar with PQ, found this method little difficult
It's really helped me...tq
that's amazing. thank you very much.
I really like this series. A quick question. How can I add the UNIQUE function to Excel 2016?
So helpful, thanks Leila!
Thank you Chris!
Thanks for sharing
Thank you very much
Beautiful and tempting
Hey Leila Congratulations on 1M subscribers
Thank you!
for grand total hide - simply go to design >grand total>off rows and columns
Nice Video thanks
Outstanding. Thank you
My pleasure!
I have been noticing that your subscribers are growing very fast!! Could you please make a video on how to grow on TH-cam as a small channel? It would be useful.... And Great Video 🔥🔥🔥
Bravooo 👏👏👏👏
Good job
Super Amazing!
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?
Hi Leila, when is Dax course coming out eagerly waiting for it to learn more about it
I am looking for this type of tutorial.Cute
Wow... Great learning
Thank you
Thanks for this video and all the rest. Very interested to see the whole DAX and Data Modelling in Udemy 😍
If you want your structured references to be absolute you can use this syntax
TableName[[Column1]:[Column1]]
In case of this video it would be
TProject[[Project]:[Project]]
Yeah I used this in my last dashboard and it was a really life saver
Wouah, amazing !!!
Hey Leila, instead of having the data concatenated in same cell, is it possible to have it displayed in multiple rows. Is there a solution to achieve this result?
@leilagharani can I use still use the concatenatex as a Dax function if I just want to return just one text?
Obrigado 👏👏👏👏👏👏👏
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.
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...