Convert Single-Column Values into Structured Rows and Columns with M query

[v-xinruzhu-msft]

When preparing data, we may meet a scenario that needs to split all values stored in a single column into structured rows and columns. Here I will share a simple method with M query.  

Usage scenario: 

Consider we have sample table as below: 

We hope to have the following outcome: 

Operation steps: 

1.Use Group by feature to aggregate table rows based on Category group and select All Rows operation.  

This will generate a step using Table.Group function. In the formula bar, you will have:  

2.Modify the Table.Group function to invoke List.Split function and use current contents item count as parameter.  

List.Split(_[Field], List.Count(_[Field])/2) 

Notice:  

At the above expression '2' is a variable which means the final result has 2 groups, it can be changed if you want each group to have fewer items and you will have more groups. 

Since List.Split only support whole number as optional parameter, we also need to add Number.Round function to handle the excepted result: Number.Round(List.Count(_[Field])/2) .

3.Invoke above function with List.Transform to combine contents with comma. 

List.Transform(List.Split(_[Field], Number.Round(List.Count(_[Field])/2)), each Text.Combine(_,",")) 

Now in the formula bar, you will have: 

4.Expand the field values. 

5.Use 'Split column by delimiter' to expand field values to columns and rename the result column names. 

Reference links: 

How to GROUP BY or summarize rows - Power Query | Microsoft Learn 

Table.Group - PowerQuery M | Microsoft Learn 

List.Split - PowerQuery M | Microsoft Learn 

Author: Xiaoxin Sheng

Reviewer: Ula and Kerry

Split list to column.pbix