Power Query - Importing table and adding row below into a new column

Hi,

Really new to this power query malarkey and I am being told it is the way to go when cleaning data.

I'm not sure if this can be done but I suppose there is no harm in asking.

I sometimes get data where the value of cell b1 will appear in a2

So it should be.

img1.jpg

But when importing the spreadsheet it appears as

img2.jpg

Is there a way of programming Power Query to putting the value from A2 into a new column aligned against cell A1 or at least combining them so A1 and A2 are in the same cell. I am sure there will be some conditional formatting involved.

I also have hundreds of rows that I need this to be applied to so the sample file is only a snippet of what I need it to do.

I attach a sample file with the required result A as a combined cell or B as 2 separate columns?

Can it be done?PowerQuery_Can this be done.xlsx

Hello! I think this is what you want.
Stages of work:
1. upload to Power Query
2. removing rows with empty values
3. adding an indexed column
4. adding a custom column with the formula Number.IntegerDivide([Index], 2). Here "2" is the number of lines that need to be numbered equally.
5. Here we group by a custom column, and using the Text.Combine function we combine cells by the column “Column1”, and also specify the separator “-”.
6. delete the custom column.
The result can be seen on the Table1 sheet in the attached file.

Perfect. Thank you

Happy to help.