Forum Discussion
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 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
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
2 Replies
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 DataHi 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
