Forum Discussion
Solved
Converting distinct rows to Columns based on criteria
Hi,
I have the following data set -
| Month | Year | Category | Sub Category | Value |
| January | 2023 | Purchase | Inventory | $50.00 |
| January | 2023 | Purchase | Equipment | $200 |
| February | 2023 | Goods | Inwards | $100 |
| March | 2023 | IT | Procurement | $1000 |
How do I convert the above data set to -
| Category | Sub Category | January | February | March | April | May | Year | ||
| Purchase | Inventory | 50 | 2023 | ||||||
| Purchase | Equipment | 200 | 2023 | ||||||
| Goods | Inwards | 100 | 2023 | ||||||
| IT | Procurement | 1000 | 2023 |
I have a table which is 20000 rows with historical data from 2010. Basically looking to categorise by different months (columns) and the category/sub category as rows.
Looking for a solution in Power Query.
Thank you for your help.
Hi sumanthdk
This is certainly not a best practice, and exclusive to Excel reporting alone, I would say.
Nonetheless, it can be done entirely through the User Interface; here is the pattern.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTIyMDIGUgGlRckZicWpQKZnXllqXkk+WNbUQClWB79y18LSzIJcoA6wLES9W2pSEaoG9/z8lGKw4eWJRWCWIVStbyLQKIRCzxCQ8UX5yaVFqVBTgSqBSmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t, Category = _t, #"Sub Category" = _t, Value = _t]), ChType = Table.TransformColumnTypes(Source,{{"Value", Currency.Type}}), MergeCols = Table.CombineColumns(ChType,{"Year", "Category", "Sub Category"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"), PivotCol = Table.Pivot(MergeCols, List.Distinct(MergeCols[Month]), "Month", "Value", List.Sum), SplitByDelimiter = Table.SplitColumn(PivotCol, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Year", "Category", "Sub Category"}) in SplitByDelimiterI hope this is helpful
2 Replies
Hi sumanthdk
This is certainly not a best practice, and exclusive to Excel reporting alone, I would say.
Nonetheless, it can be done entirely through the User Interface; here is the pattern.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTIyMDIGUgGlRckZicWpQKZnXllqXkk+WNbUQClWB79y18LSzIJcoA6wLES9W2pSEaoG9/z8lGKw4eWJRWCWIVStbyLQKIRCzxCQ8UX5yaVFqVBTgSqBSmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t, Category = _t, #"Sub Category" = _t, Value = _t]), ChType = Table.TransformColumnTypes(Source,{{"Value", Currency.Type}}), MergeCols = Table.CombineColumns(ChType,{"Year", "Category", "Sub Category"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"), PivotCol = Table.Pivot(MergeCols, List.Distinct(MergeCols[Month]), "Month", "Value", List.Sum), SplitByDelimiter = Table.SplitColumn(PivotCol, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Year", "Category", "Sub Category"}) in SplitByDelimiterI hope this is helpful
