Transforming Table

Hi Anonymous 
M- code :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooys9KTS4BsgyNDAwMwLQpmAJiEyArNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, #"Budget FY 24" = _t, #"Budget FY 25" = _t, #"Sponsoring FY 24" = _t, #"Sponsoring FY 25" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Budget FY 24", Int64.Type}, {"Budget FY 25", Int64.Type}, {"Sponsoring FY 24", Int64.Type}, {"Sponsoring FY 25", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Type"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Year"}})
in
#"Renamed Columns"

You can also apply the steps with ux of Power query :

 

I attached pbix , so you can follow 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In Power Query, start with your data...

Select both budgeting columns and both sponsoring columns and in the Transform tab > unpivot columns.

Now under the Add Column tab (not transform) Select the "Attribute" column > Extract > Last Characters > Last 2 will give your your FY number, you can rename this column to FY.

Back in the transform tab... Select your "Attribute" column again > Extract > Text before delimiter > when the box pops up just put a space as your delimiter

Time to repivot. Select the "Attribute" column again and under Transform > Pivot Column > set the Values column to Value and hit OK