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”
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 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!
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]]
@@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
I have been noticing that your subscribers are growing very fast!! Could you please make a video on how to grow on RU-vid 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?
I Have Data of Only Two Columns - First Column has Unique Value of 10K approx. which are repeated one after the other with total row counts of 400 K Plus. And in Second Column i have Unique values of 65 Counts which are repeated accordingly in relation to first Column of 400K Plus count. Can you pls help
Wow. The DAX part was work. Love your wrap up explanation at 15:28. It sounded so much more straightforward than the DAX solution. lol ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-8HakuTeijGI.html Thinking through it explained like that was just doing a sequence of two auto filters. You've shared many shortcut keys. I can't live without them. Refresh = Alt+F5 You are awesome. Many thanks!
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.
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
Hello mam please help me how to activate "Create Data Type" options under power query, i have O365 office licence also but above option are not showing in my excel power query please help me how can i deal with it🙏
I don't script the screencast part. It would be far too complicated for me. I set things up and do a dry run (sometimes a few dry runs) until I am happy with the flow and then record it all. I script the headshot parts so they're smooth.
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
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?
What if there is duplicate rows, will it shows data 2 times or single Ex: if Leila name in the sales department twice, I wanted return one time in powerquery. I can do that in formula.
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.
Hi Leila, Is there any way to do this but the other way? For example: When I have a parent code in column A, Child code1 in column B, Qty of child code1 in column C, Child code2 in column D, Qty of child code2 in column E. What I want is to populate and stack a parent code in column A as many times as it has child codes and then get all child codes in column B and the qtys in column C. Can Power Query do it? Thanks!
Hi, i follow your youtube channel which is very good for learning. I have one query, Can i convert excel file into xml file. In excel i have headings in multiple coloums and data in multiple rows. I want to generate individual xml file for each row. Thanks
Hi Leila, thx for your professional videos. I have a question to solution 1: If there is would be added in your data table a new Project, it would show up in column E, but how can we ensure, that the formulas in range F3 to H6 dynamically go a additional line?
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. 🤔🤗
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...
From all the RU-vidrs 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
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.
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 😘
Hi Leila, what a wonderful tutorial, I have a question, what if I want to combine time type, I have tried but fail with formula or even query, not continue with Dax, thx
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
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!
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...
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
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!!
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.
