Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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
Check out the July 2025 Power BI update to learn about new features.