Share [Social9_Share]

Convert Multiple Columns in Rows

Convert Multiple Columns into Rows

Someone asked this interesting question on the PowerBI forum. Interesting enough that it deserves a blog post and could be relevant problem for a lot of people around

Consider this really messy data

Convert Multiple Columns into Rows 2

Against each continent, country and city we have 2 metrics revenue and quantity for multiple years

 

How would you transpose this in a tabular format ?

This is how the expected result should look like

Convert Multiple Columns into Rows 3

Please don’t say cut, copy and paste. You really need a heart to transform the data that way! I’ll post my solution soon!

DOWNLOAD THE EXCEL PROBLEM HERE

 

Watch the Solution Video ..

 

DOWNLOAD THE SOLVED FILE

 

 

Other Power Query Problems

  1. Rank Data based on 2 conditions
  2. Repeat the Row N Times

 

 

  • GraH

    Transform is the key word here…

  • GraH

    Could it be that “Revenue” should be in E4 and not D4?
    I’m getting close, but not yet there…

    • I’ll post my reply by Monday! Nevertheless you can see this link – https://community.powerbi.com/t5/Desktop/unpivot-with-multiple-measures/m-p/352277#M158470

      • GraH

        I did it as followed, though I tried to find a solution where I could keep “qty” and “revenue” as title, but that didn’t work.
        So got stuck with this:
        let
        Source = Excel.CurrentWorkbook(){[Name=”rData”]}[Content],
        #”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}, {“Column4”, type any}, {“Column5”, Int64.Type}, {“Column6”, Int64.Type}, {“Column7”, Int64.Type}, {“Column8”, Int64.Type}, {“Column9”, type any}, {“Column10”, Int64.Type}, {“Column11”, Int64.Type}, {“Column12”, Int64.Type}, {“Column13″, Int64.Type}}),
        FillDownRegions = Table.FillDown(#”Changed Type”,{“Column1”, “Column2”}),
        Remove1stRow = Table.Skip(FillDownRegions,1),
        PromoteHeaders = Table.PromoteHeaders(Remove1stRow, [PromoteAllScalars=true]),
        UnpivotColQty = Table.UnpivotOtherColumns(PromoteHeaders, {“Column1”, “Column2”, “Year”, “2013_1”, “2014_2”, “2015_3”, “2016_4”, “2017_5”}, “Attribute”, “Value”),
        UnpivotColRevenue = Table.UnpivotOtherColumns(UnpivotColQty, {“Column1”, “Column2”, “Year”, “Attribute”, “Value”}, “Attribute.1”, “Value.1”),
        RemovColYears2 = Table.RemoveColumns(UnpivotColRevenue,{“Attribute.1”}),
        RenameColumns = Table.RenameColumns(RemovColYears2,{{“Column1”, “Continent”}, {“Column2”, “Country”}, {“Year”, “City/STate”}, {“Attribute”, “Year”}, {“Value”, “Qty”}, {“Value.1”, “Revenue”}})
        in
        RenameColumns

      • GraH

        Looked at it. I like the twist of the early delete of the first row and promoting the headers to get the years in the titles combined with the unpivot and pivot steps. In the several ways I tried to solve it, “Qty” and “Revenue” appear everywhere except where I wanted then too. Finally deleted them also, but more out of despair then a plan of attack.
        It just shows again that understanding the data pattern is a key element to come up with efficient PQ solutions. In combination with knowing your armory of functions. Then again, that is somewhat always the case with Excel challenges I reckon.

        PS: Now we know what you were up to lately. Instead of blogging, you were getting inspiration on other fora. 🙂

  • Jill

    this just opened so many doors for me. Amazing. Thank you.

  • a.rakesh patro

    Very nice post Chandeep. Amazing. Thank you.
    I understood everything except below error.
    I got an error while i tried t change directly in formula bar from “Attribute” to “Year”.

    https://uploads.disquscdn.com/images/7db0de855b4a22dbb1b3daa3ad38d4dcd278979334c361fd2b31f15757931157.png

    • The error says.. Year already exists. Change the name to something else. It should work!

      • The other mistake could be that you din’t rename all the column headers. Years should be renamed as City

Chandeep Chhabra

Popular Posts

© Goodly