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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
anvikuttu
Advocate I
Advocate I

Help with M or Power query transformation

Hi Team,@edhans, @ImkeF

 

Need your help with the M query.  I have rows of strings of data in Excel. I would want the power query to extract the values in such a way the keys like Groceries /Vegetables/Fruits should be transformed dynamically to columns with that name and all the respective values to be filled under these columns. 

 

Kindly help!

Thanks in advance.

Vijay

 

Description(Excel Rows)
"Groceries":"Rice"|"Vegetables":"Tomatoes"
"Groceries":"Wheat"|"Fruits":"Apple"
"Fruits":"Orange"|"Oil":"Palm"

 

anvikuttu_0-1643868866719.png

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @anvikuttu 

 

You could try the following steps:

 

1. Add an Index column starting at 1; (You may have errors in Step 5 without adding this index column.)

vjingzhang_0-1644301432171.png

 

2. Split Description column into rows by "|";

22020801.jpg

 

3. Split Description column into columns by colon;

22020802.jpg

 

4. Change column names; (If you want to display your expected output in a table visual, stop at this step and apply the changes.)

 

5. If you want to have the expected output in Power Query, select "category" column and click Pivot Column. Select value column as Values and select Don't Aggregate

22020803.jpg

 

6. Remove Index column if you don't need it.

 

Here are all M codes. You can create a new blank query, open its Advanced editor and replace any code there with below code to see detailed steps. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilFyL8pPTi3KTC2OUbKKUQrKTE6NUaqJUQpLTU8tSUzKgYqH5OcmluSDOEqxOpjawjNSE0vA+tyKSjNLIIKOBQU5qXANSBL+RYl56RBr/DNzwEIBiTm52JQGZOalJkLMwecoLCbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Description", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Description"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Description", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Description", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Description.1", "Description.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Description.1", type text}, {"Description.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Description.1", "category"}, {"Description.2", "value"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[category]), "category", "value")
in
    #"Pivoted Column"

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @anvikuttu 

 

You could try the following steps:

 

1. Add an Index column starting at 1; (You may have errors in Step 5 without adding this index column.)

vjingzhang_0-1644301432171.png

 

2. Split Description column into rows by "|";

22020801.jpg

 

3. Split Description column into columns by colon;

22020802.jpg

 

4. Change column names; (If you want to display your expected output in a table visual, stop at this step and apply the changes.)

 

5. If you want to have the expected output in Power Query, select "category" column and click Pivot Column. Select value column as Values and select Don't Aggregate

22020803.jpg

 

6. Remove Index column if you don't need it.

 

Here are all M codes. You can create a new blank query, open its Advanced editor and replace any code there with below code to see detailed steps. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilFyL8pPTi3KTC2OUbKKUQrKTE6NUaqJUQpLTU8tSUzKgYqH5OcmluSDOEqxOpjawjNSE0vA+tyKSjNLIIKOBQU5qXANSBL+RYl56RBr/DNzwEIBiTm52JQGZOalJkLMwecoLCbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Description", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Description"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Description", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Description", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Description.1", "Description.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Description.1", type text}, {"Description.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Description.1", "category"}, {"Description.2", "value"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[category]), "category", "value")
in
    #"Pivoted Column"

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

BA_Pete
Super User
Super User

Hi @anvikuttu ,

 

Paste this code over the default code in a new blank query to follow the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KT04tykwttopRCspMTo1RqolRCktNTy1JTMpJLY5RAoqH5OcmluSDOEqxOqhawjNSE0vAetyKSjNLIOodCwpyUqGKIcJAQf+ixLx0iPH+mTlgdQGJObkgZbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column = _t]),
    splitByDelim1 = Table.SplitColumn(Source, "column", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"column.1", "column.2"}),
    col1 = Table.SelectColumns(splitByDelim1,{"column.1"}),
    col2 = Table.SelectColumns(splitByDelim1,{"column.2"}),
    renCol1 = Table.RenameColumns(col1,{{"column.1", "column"}}),
    renCol2 = Table.RenameColumns(col2,{{"column.2", "column"}}),
    appendCols = Table.Combine({renCol1, renCol2}),
    splitByDelim2 = Table.SplitColumn(appendCols, "column", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"category", "item"})
in
    splitByDelim2

 

Summary:

1) Split original column by pipe delimiter (|)

2) Create two sub tables of only one column each (column.1, column.2)

3) Rename sub table columns to just [column] so they match

4) Append sub tables

5) Split appended column by colon delimiter (:)

6) In the final step, renamed the split columns by changing these values:

BA_Pete_0-1643888799882.png

 

This gives me the following output:

BA_Pete_1-1643888899382.png

 

You will need to use a matrix visual to display as you can't pivot this table due to some cell values resolving to null if you try.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




serpiva64
Solution Sage
Solution Sage

Hi, 

if this is what you want

serpiva64_0-1643883101471.png

you need:

in M:

create the first query

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KT04tykwttopRCspMTo1RqolRCktNTy1JTMpJLY5RAoqH5OcmluSDOEqxOqhawjNSE0vAetyKSjNLIOodCwpyUqGKIcJAQf+ixLx0iPH+mTlgdQGJObkgZbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2"})
in
#"Removed Columns"

 

then the second query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KT04tykwttopRCspMTo1RqolRCktNTy1JTMpJLY5RAoqH5OcmluSDOEqxOqhawjNSE0vAetyKSjNLIOodCwpyUqGKIcJAQf+ixLx0iPH+mTlgdQGJObkgZbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.1"})
in
#"Removed Columns"

 

then append them in a new query:

let
Source = Table.Combine({#"Table (2)", #"Table (3)"}),
#"Merged Columns" = Table.CombineColumns(Source,{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Merged Columns", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
#"Sorted Rows" = Table.Sort(#"Split Column by Delimiter1",{{"Merged.1", Order.Ascending}})
in
#"Sorted Rows"

 

then load data to power bi and create a matrix like the one i posted before with this data:

serpiva64_1-1643883394936.png

you need then to format  it

 

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors