Very clear explanation. I was wondering if you might be able to explain how to adapt following additional requirement: when starting a new group I'd like to have the index to continue rather than being reset.
Thank you for your time. What if I want power query to generate steps that sum up the distinct rows... Can you help please. For instance, I want the sum of Smart TV instead of having the item repaeted twice or more
How would I return a count for the number of instances? Let's say I only want to know how many times each product occurs in the dataset, how would I only view those numbers?
@@ExcelExciting based on your example, if product names have total and want to ignore count of that particular product total. For example Product Amt Count Laptops 10 1 Laptops 10 2 Laptops Total 20 Want to ignore that count of laptops total.
Well in that case before you apply the index step.. filter the product column doesn’t contain total 👍🏻, that will wipe of the the total row of each product from the list...
now i got you..mate !! there are many ways to do it.. but best solution I can share you will be creating a customs column looking for the text contains "Total" return "Count" else "null" that will not take in consideration to count the "Total" row
Please advise, if instead of occurrence, I want to bring the total occurrences in front of each occurrence, how could that be done? Example, if Headphone appears twice, the first occurrence would show1 and the second one will show 2. However, if I want to see 2 both the times how could that be done?
What I have is a column containing text (Department). for example 10 saying SALES then another 10 saying ADVERTISING then another 10 saying MARKETING. I import into Power Query. All good. When I export to Excel I want to use that column in a Slicer. The slicer automatically sorts. I want the order in the original order. So what I need to do is to add a column in the Power Query which will have 10 x 1, then 10 x 2 and then 10 x 3. When I export back to Excel I can then use Power Pivot to sort the Department column using the above number column with numbers. It almost seems like I should be using Index and Modulo, but its not the same thing. So all I want to do is to have a new column in Power Query - starting at 1 and while the DEPARTMENT is SALES put a 1, then when it changes to ADVERTISING have 10 x 2's etc. The increments are the same.
I’m trying to get you.. so you are trying to make two index column one outside department & entries inside the department.. you can still sort your data in PQ.. if you can attach link of sample data with your desired output manually made that would great & I might be able to help you out
Very nice tutor master.. Can you help me.. if i want to create before after product Ex : 1/2/19 Uno 1/10/19 PlayStation 1/12/19 Nintendo So i will create Before = PlayStation After = Nintendo Can you help me please
Well, cannot it more easier? In excel, I will do a simple Countif( Locked First Cell:Criteria, criteria) ? bit confused about the simplicity of power query here, if you work on tables, Update too is automatic, what am I missing here. Is there a better way now?
Check the names of the formula as power query is case sensitive. If you still unable resolve it send me the screenshot on Facebook messenger facebook.com/fshaikhExcelMaster/
I like it! That's a tough challenge to solve.
And I saw those balloons! :)
😄
Thanks.. hope you like it..!!
Fantastic! I have been searching this for hours and it could be days if I had not bumped into this video! Thank you Sir!
That's really awesome!! I was able to achieve the same requirement in Power BI using your steps. Thanks a Lot!!
Welcome!! Glad to hear your query was resolved 😃😘
This is SO helpful. Thank you so much for posting this!
thank you so much ☺️ .. what are your daily challenges with excel
My friend - this is brilliant. Thank you - you helped me with problem today
Love the solution - thank you so much for sharing
Glad it was helpful!
Thank you so much, Faraz. That's extremely helpful!
THANK YOU SO MUCH YOU SAVED MY LIFE PLEASE COM TO BRAZIL SO I CAN HUG YOU
very useful simple and great, God bless you brother
This is an alternative way for using the countif formula in the power quarry.. Thanks a lot
Welcome.. what are other challenges you have with your data
AWESOME video, thanks so much!
Thank you 🙏🏻
Thanks for helping me, now i can do my job easily
Awesome 👏🏻!!! Glad to hear that.. what are your other challenges in business ??
Very clear explanation. I was wondering if you might be able to explain how to adapt following additional requirement: when starting a new group I'd like to have the index to continue rather than being reset.
Been looking for a solution to this since yesterday, so glad I found this - thank you sir!
Cool.!!! You know the trick now !!!
Nice, thanks!
Excellent explanation
Thanks Nitin 👍🏻, glad to hear you find it helpful
Very nice tutorial.
Thank you!! In what real case scenario have you used this solution ?
You're great sir
Thanks Dear
Nice video 👍
Can we do same thing for calculated columns in DAX?
Thank you for your time. What if I want power query to generate steps that sum up the distinct rows... Can you help please. For instance, I want the sum of Smart TV instead of having the item repaeted twice or more
Thank you !!👏
Welcome, glad to hear you find it helpful
Doing good son.. keep it up
Thank you
How would I return a count for the number of instances? Let's say I only want to know how many times each product occurs in the dataset, how would I only view those numbers?
what to do if i want to keep the same serial number for those data that are same in the column
Amazing trick. thanks for sharing PQ fun...
Hope you enjoyed & share with others too 👍🏻
@@ExcelExciting based on your example, if product names have total and want to ignore count of that particular product total.
For example
Product Amt Count
Laptops 10 1
Laptops 10 2
Laptops Total 20
Want to ignore that count of laptops total.
Well in that case before you apply the index step.. filter the product column doesn’t contain total 👍🏻, that will wipe of the the total row of each product from the list...
@@ExcelExcitingin this case sum of product total will calculate but will ignore count as mentioned above?
now i got you..mate !! there are many ways to do it.. but best solution I can share you will be creating a customs column looking for the text contains "Total" return "Count" else "null" that will not take in consideration to count the "Total" row
Thank you Faraz
My pleasure
Please advise, if instead of occurrence, I want to bring the total occurrences in front of each occurrence, how could that be done? Example, if Headphone appears twice, the first occurrence would show1 and the second one will show 2. However, if I want to see 2 both the times how could that be done?
Very good question what do you need to do is group by and make the count of the product
Nice
Thank you 😊
Thanks!
Welcome!
What I have is a column containing text (Department). for example 10 saying SALES then another 10 saying ADVERTISING then another 10 saying MARKETING. I import into Power Query. All good. When I export to Excel I want to use that column in a Slicer. The slicer automatically sorts. I want the order in the original order. So what I need to do is to add a column in the Power Query which will have 10 x 1, then 10 x 2 and then 10 x 3. When I export back to Excel I can then use Power Pivot to sort the Department column using the above number column with numbers. It almost seems like I should be using Index and Modulo, but its not the same thing. So all I want to do is to have a new column in Power Query - starting at 1 and while the DEPARTMENT is SALES put a 1, then when it changes to ADVERTISING have 10 x 2's etc. The increments are the same.
I’m trying to get you.. so you are trying to make two index column one outside department & entries inside the department.. you can still sort your data in PQ.. if you can attach link of sample data with your desired output manually made that would great & I might be able to help you out
Thanks sir, pls guide me how can we have 1 for HeadPhone on each occurance.
You don't need to give the increment value while adding the index column.
@@ExcelExciting sir, given sol. Is not working, it gave 1 to all
Very nice tutor master..
Can you help me.. if i want to create before after product
Ex :
1/2/19 Uno
1/10/19 PlayStation
1/12/19 Nintendo
So i will create
Before = PlayStation
After = Nintendo
Can you help me please
Hi, Thanks for this share. But when I apply this on a data of 10K+rows, it takes a lot of time to load in to excel. Any help on this?
Loading the data into Excel is totally depending on your system resources.
Good ❤️
Thank you !!
Well, cannot it more easier? In excel, I will do a simple Countif( Locked First Cell:Criteria, criteria) ? bit confused about the simplicity of power query here, if you work on tables, Update too is automatic, what am I missing here. Is there a better way now?
You can post your query on our forums excelexciting.com/forums/ by attaching excel file..
@@ExcelExciting Thank you Faraz.
Hi I did this method but when I expand the table, it creates duplicates. Could you tell me how to fix this? Thank you!
Could you please attached the Google/OneDrive link of the file to see the data and steps. Possibility you data might have duplicates records
how did you get that dark theme?
Go to file > about you will find theme color change option
SIR WHEN I AM GROUPING ALL ROWS IT IS SHOWING SYNTAX ERROR HOW TO REMOVE IT
Check the names of the formula as power query is case sensitive. If you still unable resolve it send me the screenshot on Facebook messenger
facebook.com/fshaikhExcelMaster/
Boss
Yes boss ;)
Nice
Thanks Asif