transform budget table

Question

Hi Guys, I need help to transform table my source is excel file and it looks like this:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; January (marged 4 column)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;February(marged 4 column&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;an so on&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Plan 2019&nbsp; &nbsp; Fakt 2019&nbsp; &nbsp; plan 2020&nbsp; &nbsp; fakt 2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Plan 2019&nbsp; &nbsp; Fakt 2019&nbsp; &nbsp; plan 2020&nbsp; &nbsp; fakt 2020&nbsp;Income&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 110&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 202&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 130&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60Salary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;55&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15Expanses&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 90&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50&nbsp;&nbsp;Guys need suggestions how to transform that kinda information for sothing more useful?I gues I need transform in something like this:Service&nbsp; &nbsp; &nbsp; Year&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Month&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; plan/fact&nbsp; &nbsp;&nbsp;Income&nbsp; &nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;January&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;plan&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100Salary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; January&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fact&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;22&nbsp;and so on a lot of rows, i have no idea how do that or maybe any ather suggestions?As always sorry for my english..............

v-yingjl · Accepted Answer

Hi&nbsp;AndrejZevzikov&nbsp;,
Based on your description, I have created an excel sample like this:

&nbsp;
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"

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

Forum Discussion

transform budget table

2 Replies

Related content

Table Transformation

Table Transformation

Table Transformation

Working with Budgets

Transform columns / type