Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

v-xinruzhu-msft

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

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: 

vxinruzhumsft_0-1719562305817.png

We hope to have the following outcome: 

vxinruzhumsft_1-1719562305821.png

Operation steps: 

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

vxinruzhumsft_2-1719562341957.png

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

vxinruzhumsft_3-1719562341960.png

 

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) 

 

 

vxinruzhumsft_4-1719562488073.png

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: 

vxinruzhumsft_5-1719562488074.png

 

4.Expand the field values. 

vxinruzhumsft_6-1719562544233.png

 

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

vxinruzhumsft_7-1719562544235.png

 

  

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

 

Comments

Oh wow Xiaoxin! 
I think that solution could work for a data modeling challenge I've solved other ways that may not be as effective!  Thank you for sharing. 

the same result can be obtained like this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcyxDYAwEATBVtDHTvx/RwsUYbkGEJL7JyCxNpxkxojrvddz9GjRY7bdCRcs2PC5OfEn/sSf+BN/4i/8hb/wF/7CX/iFX/iFX/iFX/iN3/iN3/iN3/8/Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Field = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Field", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Content", (x)=> Table.FromRows( List.Split( x[Field],3),{"Col1","Col2","col3"})}}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Grouped Rows", "Content", {"Col1", "Col2", "col3"}, {"Col1", "Col2", "col3"})
in
    #"Expanded Content"