How to shift down 1 row for only 1 column in M?

[powerbiuser101]

Hi,

I have a similar dataset as below (row 0 is the headers here):

Column1	Column2	Column3	Column4
Date	Column1	Column2	Column3
20180320	ReportName	Gross	Profit
20180319	EvidenceA	5	3
20180318	EvidenceB	10	8
20180317	EvidenceC	15	13

After extracting data out of a multiple excel files, I'm at the point in query where I'm ready to promote headers but as you can see, it's not clean. I either want to:
1. Shift column 1 down one, remove the 1st row and promote the new 1st row to headers

2. Shift all the other columns (except column 1) up 1 and then promote the 1st row to headers

Is there a way to shift up/down for only 1 column? I've read an example of using index+nested joins but I got lost and it didn't make sense.

[Zubair_Muhammad · Accepted solution]

Hi @powerbiuser101

I know of one way.. I do not know if it is ideal

Please check the attached file here

[Zubair_Muhammad · Accepted solution]

Hi @powerbiuser101

I know of one way.. I do not know if it is ideal

Please check the attached file here

[Zubair_Muhammad · Accepted solution]

@powerbiuser101

Here is the process

1) Create a duplicate Query

2) Remove the first column in original Query

3) Remove the other 3 Columns in Duplicate Query

4) Promote the relevant rows as headers in both Queries

5) Add an Index Column in both Queries

6) Now merge both queries using full outer join

[Anonymous]

This solution is manual but what we should do to dynamically shift 1 column down or up?

[Anonymous]

@Anonymous 
There's a way to do in Power Query, assuming there is something that will trigger when the data starts vs. any other rows that should be removed.

 Basically would write function that would look for this "anchor" and remove the rows above it. It would be flexible enough to not always assume it would only 1 row. 

If you start a new post, and add some sample files I can probably put a solution together. If you do make a new post ( I wouldnt clog up this post) please be sure to tag me.

-Nick

[powerbiuser101]

Works perfectly, thanks @Zubair_Muhammad!