Awesome examples! Thanks for providing the sample file to follow along. Note that for example #1, you can also directly transform the index column with Integer-Divide Column by 6 and you will get six 0s, six 1s and six 2s which then allows you to Pivot on Field Name and get the data in rows 0, 1, 2. Just another way to get there. Thanks for your lessons! Very helpful :)) Thumbs up!!
Hi Wayne, as always thank you for the support! Integer-Divide would definitely work in example 1 agreed, as there is a fixed number of Field Names for each customer. I wanted to future proof the query as much as possible for instances where there could be a varying amount of Field Names, so the first customer could have 5 field names and second customer could have 6 and so forth, hence the conditional column enables us to make this part of the query dynamic. Super glad the lessons are helpful! 😀
You're most welcome. You can download the practice example here: docs.google.com/spreadsheets/d/1kZ4_SJU5B2gascUDCKtdg9PiYJLr8eLs/edit?usp=sharing&ouid=108573460979392589182&rtpof=true&sd=true
Hi Arden you're most welcome :). We don't know the exact number of Field Names our source data may have each time, for example, one month may have "Customer", "Sales Rep", "Sales Order Number", "Product", "Sales Value", so that’s 5 rows and the next month may have those 5 rows plus another 2 rows for example "Product Returned" and "Product Value Returned" and the next month may go back to only having 5 rows. So each month the Field Names may vary, so we need our Field Names to be dynamic and we do this by adding the Index Column and the Conditional Column. So the Index Column is a helper column to show Power Query the number of the first Field Name, in this example it's "Customer" then when using the Conditional Column we can return all the Index numbers for the "Customer" field only, then once those index numbers are returned by the Conditional Column, we can fill them down to the rest of the rows, then group them. So all the field names that follow the first field name, in this case "Customer" can be grouped together by the same index number. And this is required as we don't know how many field names each month may have and assists us in making the query dynamic. Hope this helps!
You have NO IDEA how much I've been looking for this. You just earned a Subsribe a comment and a like.
Glad it helped! Thank you for your support :)
@@MissMicrosoft pleasure ❤️ this is going to make me a hero at my job
@@PatrickPitso I love hearing this!!!
Awesome examples! Thanks for providing the sample file to follow along. Note that for example #1, you can also directly transform the index column with Integer-Divide Column by 6 and you will get six 0s, six 1s and six 2s which then allows you to Pivot on Field Name and get the data in rows 0, 1, 2. Just another way to get there. Thanks for your lessons! Very helpful :)) Thumbs up!!
Hi Wayne, as always thank you for the support! Integer-Divide would definitely work in example 1 agreed, as there is a fixed number of Field Names for each customer. I wanted to future proof the query as much as possible for instances where there could be a varying amount of Field Names, so the first customer could have 5 field names and second customer could have 6 and so forth, hence the conditional column enables us to make this part of the query dynamic. Super glad the lessons are helpful! 😀
Really appreciate this tutorial! Helped so much
I’m really glad to hear that!
Thanks Madam for Excellent Video
You're so kind, and you're most welcome Kebin!
Thank you so much for your videos 🙂
You are so welcome!
5:20 on that step the add columng tab standard is grey out, what could be wrong?
Are the data types of the columns that you’re using Standard on the same?
Thank you
Only a pleasure Jashwanth :)
Fantastic, thank you so much.
You're most welcome Victor :)
Excellent
Thank you Sajid for the support!
Thanks for the sharing
Request you to please share excel file link.
You're most welcome. You can download the practice example here: docs.google.com/spreadsheets/d/1kZ4_SJU5B2gascUDCKtdg9PiYJLr8eLs/edit?usp=sharing&ouid=108573460979392589182&rtpof=true&sd=true
Thanks
You’re welcome
Hi thanks for this! However, i don’t understand the need for index and conditional column? Would pivoting without those steps work?
Hi Arden you're most welcome :). We don't know the exact number of Field Names our source data may have each time, for example, one month may have "Customer", "Sales Rep", "Sales Order Number", "Product", "Sales Value", so that’s 5 rows and the next month may have those 5 rows plus another 2 rows for example "Product Returned" and "Product Value Returned" and the next month may go back to only having 5 rows. So each month the Field Names may vary, so we need our Field Names to be dynamic and we do this by adding the Index Column and the Conditional Column. So the Index Column is a helper column to show Power Query the number of the first Field Name, in this example it's "Customer" then when using the Conditional Column we can return all the Index numbers for the "Customer" field only, then once those index numbers are returned by the Conditional Column, we can fill them down to the rest of the rows, then group them. So all the field names that follow the first field name, in this case "Customer" can be grouped together by the same index number. And this is required as we don't know how many field names each month may have and assists us in making the query dynamic. Hope this helps!
I love you❤
Grea.......t
Thank you 😊