Power Query Transforming Column with Actual and Budget Amounts

[legalhustler]

I have the following three columns of data in Power Query that looks like this:

Category...……….Month...……..............Amount

Sales...…………….Jan Actual...……………......$100
Sales...…………….Jan Budget...................$90
Sales................Feb Actual....................$200
Sales................Feb Budget..................$250
Legal.......…………Jan Actual...……………......$500
Legal.......…………Jan Budget...................$400
Salaries...…………Feb Actual...……………......$550
Salaries...…………Feb Budget...................$450
Marketing...………Jan Actual...……………......$45
Marketing...………Jan Budget...................$40
Marketing...………Feb. Actual..……………......$80
Marketing...………Feb Budget...................$85


I need to transform the table so there is four columns that looks like this:


Category..........Month.........................Actual................Budget
Sales...…………….Jan...........……………......$100..................$90
Sales................Feb...........................$200...................$250
Legal.......…………Jan...…………….............$500.................$400
Legal.......…………Feb.............……………...$550..................$450
Marketing...………Jan.........……………......$45.....................$40
Marketing...………Feb.........……………......$80......................$85


How do I get it into the above format?

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Month", type text}, {"Amount", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Month", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Month.1", "Month.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Month.1", type text}, {"Month.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Month.2]), "Month.2", "Amount", List.Sum)
in
    #"Pivoted Column"