Forum Discussion
Excel data split from multiple column to row
Here are the formulas in a version you can paste and fill down:
=IF(MOD(ROW()-ROW($D$7),COLUMNS($B$1:$K$4)),"",INDEX($A$1:$A$4,INT((ROW()-ROW($D$7))/COLUMNS($B$1:$K$4))+1,1))=INDEX($A$1:$K$4,INT((ROW()-ROW($D$7))/COLUMNS($B$1:$K$4))+1,MOD(ROW()-ROW($D$7),COLUMNS($B$1:$K$4))+2)you can either replace the ranges like $B$1:$K$4 with the correct range in each case or I would recommend replacing it with a named variable (e.g. DATA) then you create in the Name Manager under the Formula menu. BTW the $D$7 in these formulas are the first cell where the data will go so that ROW()-ROW($D$7) gives you a count.
mtarler what needs to do if I want output like
Not quite sure this is doable with the Power Query user interface only or it's going to be complex
See attached file with a possible option where I decomposed the steps with hopefully meaningful names
You'll see some steps are very similar. This means the query can we written differently. That would make sense if we were to deal with more than 2 sets of columns (Code, Price) but as you're new to this I kept things "simple"