• We are receiving reports of members using the private messaging service (Conversations) in ways that break the forum rules:
    • Do not invite another member to take the question off the forum (i.e. do not suggest that they post to a different forum, do not suggest that they email or private message you the problem, do not simply post a link to another forum, unless it is to a specific, relevant, thread). This applies equally to members asking or answering questions.
    • Soliciting business for yourself is not permitted. This is an all volunteer board, so offering solutions in exchange for compensation is not permitted. Likewise, members seeking solutions must not offer compensation for them. If you have an urgent need, check the Consulting Services page.
    Please help us out by clicking the Report link on any messages you receive that violate these rules. Thank you
  • If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.

Power Query Transforming Column with Actual and Budget Amounts

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,062
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?
 
Last edited:

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,034
Office Version
2019
Platform
Windows
Code:
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"
 

Forum statistics

Threads
1,077,962
Messages
5,337,434
Members
399,147
Latest member
Raviteja KOTHA

Some videos you may like

This Week's Hot Topics

Top