Expanding Tables After Grouping

[Zoombini]

I'm having some difficulty getting some XML based data into a usable format. After a little cleanup it looks like this:

The red line shows the cutoff where they start repeating. Ultimately I'd like to group these and use them as my columns. After grouping it looks like this:

I need a way to expand those tables into their own columns using just the data columns from each table. Then I can transpose the whole thing and have a functional data set. Any help is appreciated.

[v-shex-msft]

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

[MarcelBeug · Accepted solution]

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.