Forum Discussion
Help with M or Power query transformation
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" |
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.)
2. Split Description column into rows by "|";
3. Split Description column into columns by colon;
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.
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.
3 Replies
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.)
2. Split Description column into rows by "|";
3. Split Description column into columns by colon;
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.
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.Hi,
if this is what you want
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:
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 !
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 splitByDelim2Summary:
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:
This gives me the following output:
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
