Data Model

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