cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerbiuser101 Regular Visitor
Regular Visitor

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

Hi,

 

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

Column1Column2Column3Column4
DateColumn1Column2Column3
20180320ReportNameGrossProfit
20180319EvidenceA53
20180318EvidenceB108
20180317EvidenceC1513

 

 

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.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

Hi @powerbiuser101

 

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

 

Please check the attached file here

 

Shift 1 row 1 column.png

Super User
Super User

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

@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

5 REPLIES 5
Super User
Super User

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

Hi @powerbiuser101

 

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

 

Please check the attached file here

 

Shift 1 row 1 column.png

Super User
Super User

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

@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

powerbiuser101 Regular Visitor
Regular Visitor

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

Works perfectly, thanks @Zubair_Muhammad!

vivekn New Member
New Member

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

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

Nick_M New Contributor
New Contributor

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

@vivekn 
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