transform budget table

[AndrejZevzikov]

Hi Guys, I need help to transform table my source is excel file and it looks like this:

                                    January (marged 4 column)                                                     February(marged 4 column                     an so on

                    Plan 2019    Fakt 2019    plan 2020    fakt 2020                 Plan 2019    Fakt 2019    plan 2020    fakt 2020

Income              100            110            200              202                           120              130                50               60

Salary                15               22              30                 25                             55                50                60               15
Expanses           15               20              15                50                              20               50                90               50 

Guys need suggestions how to transform that kinda information for sothing more useful?

I gues I need transform in something like this:

Service      Year            Month          plan/fact    

Income       2019         January           plan           100

Salary         2019          January           fact           22

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 solution]

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.

[Jimmy801]

Hello @AndrejZevzikov 

supposingly you are reading from a Excelsheet where at the beginning you have all anonymous column names. I just proposed  a similar solution today, but for sure it has adapted accordingly to your scenario. But you can get an idea how to handle all different challenges coming up. It's quite complex and needs a good knowlegde of m language. Here the link to the post I made today

Complex data transformation 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy