Forum Discussion

anvikuttu's avatar
anvikuttu
Advocate I
4 years ago
Solved

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 /Vegetab...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

     

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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:

 

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