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 Go...
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
m_dekorte
Resident Rockstar
Resident RockstarHi 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
