Transpose data skipping rows

  • A fairly simple excercise for Power Query. Load the data to PQ, highlight all data and then Unpivot the columns. Filter out the zeros, remove the uneeded columns. Here is the Mcode and the file for review.


    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"})
    in
    #"Removed Columns"
  • Hey Glenn, A pivot based approach won't help. There are multiple sheets of different companies and also the incremental data will be added as different sheets.


    Hey Alan, The product category needs to repeated for each of the transposed figure.


    Also there are multiple sheets with similar kind of information, some rows are merged which needs to skipped, the subtotals need to skipped and blank rows also needs to be skipped.


    Looking for more formula/ macro based approach.


    Thanks in advance.


    Nitz

  • Here is the new Mcode


    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type number}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", type number}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", Int64.Type}, {"Column21", type number}, {"Column22", Int64.Type}, {"Column23", type number}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", type number}, {"Column28", type number}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", type number}, {"Column32", Int64.Type}, {"Column33", Int64.Type}, {"Column34", Int64.Type}, {"Column35", Int64.Type}, {"Column36", Int64.Type}, {"Column37", type number}, {"Column38", type number}, {"Column39", Int64.Type}, {"Column40", Int64.Type}, {"Column41", type number}, {"Column42", Int64.Type}, {"Column43", type number}, {"Column44", Int64.Type}, {"Column45", Int64.Type}, {"Column46", Int64.Type}, {"Column47", type number}, {"Column48", type number}, {"Column49", type number}, {"Column50", Int64.Type}, {"Column51", type number}, {"Column52", Int64.Type}, {"Column53", type number}, {"Column54", Int64.Type}, {"Column55", Int64.Type}, {"Column56", Int64.Type}, {"Column57", type number}, {"Column58", type number}, {"Column59", type number}, {"Column60", Int64.Type}, {"Column61", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null and [Column1] <> "subtotal")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Column1"}, "Attribute", "Value"),
    #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute"})
    in
    #"Removed Columns"
    Code
    If you have multiple sheets that need to be merged into the solution, then you can add each to PQ, Append each to the other and then run your query (Mcode).  This is a  quick and easy reformatting that you are requesting.  Do a bit of research on Power Query and you may be surprised at what you can accomplish.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!