Data Model

[Prabha45]

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 comments. And merge these two tables but I did get the required table 2. What transformations I need to perform to get table 2.

Category	Sub Category	Q3- 2023 status	Q3- 2023 comments	Q4- 2023 status	Q4- 2024 comments	Owner
Road	Bike	In progress	Working	In progress	N/A	David
Road	Car	In progress	Under validation	Completed	N/A	Smith
Electronics	Phone	In progress	Working	Completed	N/A	Henry
Electronics	Laptop	In progress	Manufacturing	In progress	Manufacturing	Micheal
Electronics	Headphones	In progress	N/A	Delayed	Working	Neil
Clothes	Shirt	In progress	Transportation	Delayed	Received on Monday	Peter
Clothes	Trousers	In progress	Transportation	Completed	Done	Chris

 Table 2 - 

Category	Sub category	Quarter	Status	Comments	Owner
Road	Bike	Q3-2023	In progress	Working	David
Road	Bike	Q4-2023	In progress	N/A	David
Road	Car	Q3-2023	In progress	Under validation	Smith
Road	Car	Q4-2023	Completed	N/A	Smith
Electronics	Phone	Q3-2023	In progress	N/A	Henry
Electronics	Phone	Q4-2023	Completed	Working	Henry
Electronics	Laptop	Q3-2023	In progress	Manufacturing	Micheal
Electronics	Laptop	Q4-2023	In progress	Manufacturing	Micheal
Electronics	Headphones	Q3-2023	In Progress	N/A	Neil
Electronics	Headphones	Q4-2023	Delayed	Working	Neil

Thanks in advance

[dufoq3]

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

[Jonvoge · Accepted solution]

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