transform budget table

Hi AndrejZevzikov ,

Based on your description, I have created an excel sample like this:

After doing the following transformations in power query, you could get a result table:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Desktop\sample data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column6"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Column1"}),
    #"Lowercased Text" = Table.TransformColumns(#"Filled Down",{{"Column2.1", Text.Lower, type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Lowercased Text", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Income", Int64.Type}, {"Salary", Int64.Type}, {"Expanses", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Column1", "Column2", "Column3"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Attribute", "Column3", "Column1", "Column2", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute", "Sevice"}, {"Column3", "Year"}, {"Column1", "Month"}, {"Column2", "plan/fact"}})
in
    #"Renamed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.