Forum Discussion

CyrilMoysset's avatar
CyrilMoysset
Frequent Visitor
8 years ago
Solved

append

Hey everybody !

 

I have a table like this :

 

idtheme 1theme 2theme 3
1car  
2carbike  
3car  
4car moto
5moto  
6motobike  
7moto  
8moto  
9bikemotobike
10bike  


And I want transform this table in this format :

 

 

idtheme 1
1car
2car
3car
4car
5moto
6moto
7moto
8moto
9bike
10bike
2bike
6bike
9moto
4moto
9 bike





If someone have a simple function usable in unic query, that could help me so much ! I have already duplicate first table in 2 other tables and use append fonction to agregate values but I don't like this solution. 

 

Thanks for advance,

 

Cyril 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    CyrilMoysset,

    Have you tried unpivot transformation? Copy and paste this into your Query Editor for reference: 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpOLAKSIBSrE61kBBdJysxOVYAJG2MoNEEWyc0vyQeLmsI4CIVmCCEUI80xlVpgCllCtaEaApYyNEDIgZXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"theme 1" = _t, #"theme 2" = _t, #"theme 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"theme 1", type text}, {"theme 2", type text}, {"theme 3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Theme"}})
in
    #"Renamed Columns"

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    CyrilMoysset,

    Have you tried unpivot transformation? Copy and paste this into your Query Editor for reference: 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpOLAKSIBSrE61kBBdJysxOVYAJG2MoNEEWyc0vyQeLmsI4CIVmCCEUI80xlVpgCllCtaEaApYyNEDIgZXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"theme 1" = _t, #"theme 2" = _t, #"theme 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"theme 1", type text}, {"theme 2", type text}, {"theme 3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Theme"}})
in
    #"Renamed Columns"