append

[CyrilMoysset]

Hey everybody !

I have a table like this :

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

And I want transform this table in this format :

id	theme 1
1	car
2	car
3	car
4	car
5	moto
6	moto
7	moto
8	moto
9	bike
10	bike
2	bike
6	bike
9	moto
4	moto
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 

[Anonymous · Accepted solution]

@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"

[CyrilMoysset]

Indeed that works 

Thank you very much Nick !