Convert Rows to columns

[Anonymous]

Hi,

I have a csv file that look like this:

id	item_1	value_1	item_2	value_2	item_3	value_3	item_4	value_4	item_5	value_5
1	a	1	b	2	c	3	d	4	e	5
2	e	3
3	c	3

And I want to look like this:

id	item	value
1	a	1
1	b	2
1	c	3
1	d	4
1	e	5
2	e	3
3	c	3

With python or vba it will be easy, but I wan't to do it with power query. Is that posible?

Thank you,

[Zubair_Muhammad · Accepted solution]

@Anonymous 

This works with your sample data

Please see attached file's query editor for steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYhCdBMRGQJwMxMZAnALEJkCcCsSmSrE60WDZVKisAl4MUm2MZBZ2FBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, item_1 = _t, value_1 = _t, item_2 = _t, value_2 = _t, item_3 = _t, value_3 = _t, item_4 = _t, value_4 = _t, item_5 = _t, value_5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"item_1", type text}, {"value_1", Int64.Type}, {"item_2", type text}, {"value_2", Int64.Type}, {"item_3", type text}, {"value_3", Int64.Type}, {"item_4", type text}, {"value_4", Int64.Type}, {"item_5", type text}, {"value_5", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([value] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
    #"Removed Columns"

[Anonymous]

Thank you!