Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
8 years ago

Unpivot Table

Hi,   I have a bit of strange request not sure if it is possible but I am giving it a try.    I have the following table,       Is there a way I can duplicate the row, and put technology-...
Need Help
abukapsoun's avatar
abukapsoun
Post Patron
8 years ago

MarcelBeug

Thank you very much Marcel for this interesting walkthrough, I have applied the steps but I feel there is something not correct with the alignment.

If I am not mistaken, I haven't seen we have used the index and index/3 columns in the step that follow..

 

 

Here is the generated code. 

 

 

Thank you again,

abukapsoun's avatar
abukapsoun
Post Patron
8 years ago

MarcelBeug

 

Hi Marcel, 

 

Sorry for bothering again, but were you able to have a look at the below? 

 

Thanks again

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    Apologies for the late reaction.

     

    You made one mistake: you added a column with the Integer-Divide, you should have Integer-Divided the Index column on the "Transform Column" tab, thus transforming the Index column in the Integer-Divided column.

     

    If you corect that, it should be OK.

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      8 years ago

      On second thought, it may not work as I think you have null values.

       

      An alternative approach is to unpivot the other columns and split the Attribute column on "-".

      Then you can pivot back on column "Attribute,1"  and remove "Attribute,2" afterwards,

       

      let
    Source = Table1,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    #"Removed Columns"