Forum Discussion
Expanding Tables After Grouping
My suggestion would be another approach.
Based on your first table, if each group consists of 28 rows, than you can add an Index column starting with 0, transform this index column using (Transform - Standard - Integer-divide) by 28, and now you can pivot on the first column.
Alternatively, if your groups have a variable number of items (not always 28), but they always start with "Order Date", after adding the Index column you can add another column with formula = if [Columns] = "Order Date" then [Index] else null.
Fill down this new column, so each group of data will have its own number (the same number within each group).
Remove the original Index column and now you can pivot on the first column.
Edit: in both scenarios, you can remove the added column after pivoting.
Hi Zoombini,
For your requirement, you can add custom column with 'list.contains' and 'table.columnnames' functions to check if current table contains date column. Then expand the correspond records.
Steps:
1. Add custom column to get specific column from table.
#"Added Custom" = Table.SelectRows(Table.AddColumn(#"Removed Other Columns", "Custom", each if List.Contains(Table.ColumnNames([Data]),"Date") then [Data][Date] else null),each [Custom] <> null),
2. Expand column item to new row.
Regards,
Xiaoxin Sheng
My suggestion would be another approach.
Based on your first table, if each group consists of 28 rows, than you can add an Index column starting with 0, transform this index column using (Transform - Standard - Integer-divide) by 28, and now you can pivot on the first column.
Alternatively, if your groups have a variable number of items (not always 28), but they always start with "Order Date", after adding the Index column you can add another column with formula = if [Columns] = "Order Date" then [Index] else null.
Fill down this new column, so each group of data will have its own number (the same number within each group).
Remove the original Index column and now you can pivot on the first column.
Edit: in both scenarios, you can remove the added column after pivoting.