Help transforming data

[emsrc]

Hi,

I have a table of data that looks like this:

I would like to transform the data to look like this:

I'd like to do this in Power Query, if possible.

[AlB · Accepted solution]

Hi @emsrc 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACTQMLYAEIqxepEKxkBmUZgASMQYWIAIZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"FY20 Budget" = _t, #"FY20 Actuals" = _t, #"FY21 Budget" = _t, #"FY21 Actuals" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"FY20 Budget", Int64.Type}, {"FY20 Actuals", Int64.Type}, {"FY21 Budget", Int64.Type}, {"FY21 Actuals", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","FY","20",Replacer.ReplaceText,{"Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute.1", "Year"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.2]), "Attribute.2", "Value", List.Sum),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"Year", Int64.Type}})
in
    #"Changed Type2"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers