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,
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.
Solved! Go to Solution.
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
SplitByDelimiter
I hope this is helpful
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
SplitByDelimiter
I hope this is helpful
@m_dekorte - thank you so much. had to make some minor modifications to adjust to my working environment. but works eventually.
as a novice to power query myself, why would you say this is not best practice? if so what would be your approach?
thanks again.
Check out the July 2025 Power BI update to learn about new features.