Repeat rows according to several columns

[Mammoth789]

Hi ! 

I am actually stick into a problem that I can't figure out an issue. Here's the thing :

I have a table similar to :

Title 1	Title 2	Item 1	Item 2	Item 3	Item 4	Item 5
Mexico	Pineapple		H	H
Tokyo	Apple			X	X
New York	Strawberry	A	A	A	A	A

And I would like to transform it so that it returns :

Title 1	Title 2	Value	Item
Mexico	Pineapple	H	Item 2
Mexico	Pineapple	H	Item 3
Tokyo	Apple	X	Item 3
Tokyo	Apple	X	Item 4
New York	Strawberry	A	Item 1
New York	Strawberry	A	Item 2
New York	Strawberry	A	Item 3
New York	Strawberry	A	Item 4
New York	Strawberry	A	Item 5

In brief, there are columns 'Title ...' to keep, and the rows has to be repeated as long as there is a value in columns 'Item ...' ; We collect in new column which value was in colums 'Item...' and collect the column's title in a new column.

Thanks for helping.

[lbendlin · Accepted solution]

That is a very frequent operation in Power Query. It is called "Unpivoting other columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k2tyEzOV9JRCsjMS00sKMhJBbIVgNgDihXAOFYnWikkP7sSpNIRSRUIR0AxRJVfarlCZH5RNlAguKQosTwptaioEqQLA8fGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Title 1" = _t, #"Title 2" = _t, #"Item 1" = _t, #"Item 2" = _t, #"Item 3" = _t, #"Item 4" = _t, #"Item 5" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Title 1", "Title 2"}, "Item", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " "))
in
    #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".