Power Query Editor - Moving data from one row to another only in certain columns

[callmerain]

Hi,

I'm trying to figure out if there is any way to move data from 1 row to another (maybe based on an IF statement?) in Power Query Editor.

I have data that I am pulling in from excel file that is generated out of a system.  I am hoping to avoid having to change the excel file before import, if possible.

When I pull the raw data into Power Query Editor, there are leading rows with non or null data values.  I want to remove the leading rows and then promote the row with the Header data in it to the Headers.

The problem is that within these excel files, there is one set of Header data that is on a different row than all of the rest....

Here's an example:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

null

null

null

null

null

null

null

null

null

null

null

null

null

null

2

null

null

null

null

null

null

null

null

null

null

null

null

null

null

3

null

null

null

null

null

null

1/1/2023

2/1/2023

3/1/2023

4/1/2023

5/1/2023

4

null

null

null

null

null

null

null

null

null

null

null

Total

Max

Rent or

5

Name

Number

Class

Code

make

model

1

2

3

4

5

Mos.

Per Mth

Purchase

6

Sample1

1

3

2310

FORD

F150 SCXL

0

1500

1500

1500

0

3

1

RENT

7

Sample2

2

3

8364

FORD

F150 SCXL

0

1500

1500

0

0

3

2

PURCHASE

There are multiple excels that I need to be able to copy/paste the Applied Steps to.  In each excel, the starting point of where the dates that I need to move will be the same, but the amount of columns that have dates will change (could be a 5 month duration, could be a 22 month duration, etc).

Any ideas on this?

Bonus points if you also happen to know an easy way to combine the row data that starts after that, as it seems the Header names there are getting split between two rows.

[callmerain · Accepted solution]

Solved it!

Going to leave my solution here for posterity:

First, I duplicated my import, to have something to mold.

I used Keep Rows > Keep a range of rows to pull out only the 3 rows that I wanted to deal with (in my Sample case, it would be 3, 4, and 5)

Then I transposed them, so that I was working with 3 columns.

I added a custom column with an IF statement to check the first column for / (which only my date columns would have) and to either pull the dates or the main header info from the other column:

each if [Column1] = null then [Column3] else if Text.Contains([Column1], "/") then [Column1] else [Column3])

That gave me the solution to my main problem.  To handle that secondary word split, I created another Custom Column where if the split row cell was null, it would pull from my newly created column, otherwise it would concatonate the to row cells:

each if [Column2] = null then [Headers] else Text.From([Column2]) & " " & Text.From([Column3])

Then I removed all other columns, and transposed the Column back to a Row, and Appended my original data set onto the new row.  Promoted the top row to Headers and removed all of the unneeded rows between that and the beginning of the data