Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.