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!
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! 😀
