Forum Discussion

Prabha45's avatar
Prabha45
Helper III
2 years ago
Solved

Data Model

Hi All, I have a table(data model) like the below. For every quarter there will be 2 new columns( Status and comments). I tried unpivot the Q3, Q4 status in one table and in the other by Q3, Q4 c...
Need Help
Show and Tell
Tips and Tricks
  • Jonvoge's avatar
    Jonvoge
    2 years ago

    Hi Prabha45

     

    You may try the following M-Code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDdasMwDEZfJeS6sGfY0kEHSxn9oRclF8LWalFXMrITyNs3SVk76mw3Frakw+dzPJYbAVsuyjc641A+uAgqJ8UYh9tB9Ex8yt7XL6/DuYSObNks7owKNBvds0UtOvBkIZHwOCaX4DGhvZO2F0puIr17NEmFyYzLX074v1Q5aYWs/QzpE0KSkKFq4PYbTGp17pvP3ZqMQ/Az+BWCDWPY+Jcr9NBPOR/p10g3VuUluWl160hThtgpcAyi6cfgg7ZBg9ShLYSLWthCP2oblOgTeafSRtQ8Xwb/LXV58185pVg2zRU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub Category" = _t, #"Q3- 2023 status" = _t, #"Q3- 2023 comments" = _t, #"Q4- 2023 status " = _t, #"Q4- 2023 comments" = _t, Owner = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Sub Category", type text}, {"Q3- 2023 status", type text}, {"Q3- 2023 comments", type text}, {"Q4- 2023 status ", type text}, {"Q4- 2023 comments", type text}, {"Owner", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category", "Sub Category", "Owner"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"

     

    It Unpivots all other columns than Category, Sub Cat and Owner.

    Then it splits the new Dimension column based on rightmost space as delimiter.

    Then re-pivots the Status/Comments column to get the achieved result:

     



    _____________________________________________________
    I hope my comment was helpful.
    If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
    Find me on LinkedIn, Sessionize, or my blog Downhill Data

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Prabha45, there is a mistake in your sample data (probably)

 

If you want to preserve sort order:

 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDdasMwDEZfJeS6sGfY0kEHSxn9oRclF8LWalFXMrITyNs3SVk76mw3Frakw+dzPJYbAVsuyjc641A+uAgqJ8UYh9tB9Ex8yt7XL6/DuYSObNks7owKNBvds0UtOvBkIZHwOCaX4DGhvZO2F0puIr17NEmFyYzLX074v1Q5aYWs/QzpE0KSkKFq4PYbTGp17pvP3ZqMQ/Az+BWCDWPY+Jcr9NBPOR/p10g3VuUluWl160hThtgpcAyi6cfgg7ZBg9ShLYSLWthCP2oblOgTeafSRtQ8Xwb/LXV58185pVg2zRU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub Category" = _t, #"Q3- 2023 status" = _t, #"Q3- 2023 comments" = _t, #"Q4- 2023 status " = _t, #"Q4- 2023 comments" = _t, Owner = _t]),
    GroupedRows = Table.Group(Source, {"Category", "Sub Category", "Owner"}, {{"All", each 
        [ a = Table.Transpose(Table.DemoteHeaders(Table.RemoveColumns(_, {"Category", "Sub Category", "Owner"}))),
          b = Table.TransformColumns(a, {{"Column1", Text.Trim}}),
          c = Table.SplitColumn(b, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Quarter", "Attribute"}),
          d = Table.Pivot(c, List.Distinct(c[Attribute]), "Attribute", "Column2"),
          e = Table.TransformColumnNames(d, Text.Proper)
        ][e], type table}}),
    ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Quarter", "Status", "Comments"}, {"Quarter", "Status", "Comments"})
in
    ExpandedAll