Converting distinct rows to Columns based on criteria

[sumanthdk]

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.

[m_dekorte · Accepted 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

[sumanthdk]

@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.