Shift values to the right by N columns if cell matches a certain condition

[freelensia]

I have these columns and values

Name Location  Address         City         Country Age  Email
John    Yes    11 Main street  NY            US      23   a@gmail.com
Peter   No     23              b@gmail.com    
Mary    No     56              c@gmail.com    

As you can see, the values are not in the right columns sometimes!!

So I want to modify this table so that if Location = No, All of the values on the right should shift right by 3 columns.

For the last 3 columns that cannot be shifted, they should be ignored. Like this:

Name Location  Address         City         Country Age  Email
John    Yes    11 Main street  NY            US      23   a@gmail.com
Peter   No                                           23   b@gmail.com    
Mary    No                                           56   c@gmail.com    

Is this even possible in PQ?
@Jimmy801 @Greg_Deckler @amitchandak @parry2k @Mariusz @ImkeF  Thanks in advance!

[freelensia]

Thanks guys. Sorry I forgot to mention. My table has about 70 columns and 1 million rows. The Location column is quite early, around column number 10. Not sure which method would be most efficient but I will try them one by one.

[Anonymous]

Moving an element in a list can be done in many ways. In this case even so it could be fine ...

[Anonymous]

another way, using just the user interface.

[freelensia]

Thanks for your suggestion. Your output table looks like this:

It seems the values 23 and 56 are in the wrong place. They should be in the Age column.

Also how can I do this using the interface??

[Anonymous]

ok.

try this.

as you can see, the code steps are a series of "add custom column" functions and then delete columns and sort columns. all these functions are accessible from the UI.

[freelensia]

Manually adding and removing columns?? Lol.

[Anonymous]

The meaning of your observation is not clear, but I try to explain better the meaning of what I meant (your level of knowledge of the power query environment is not even clear to me).

A solution that involves only the use of the UI means that you can practically obtain the necessary code by clicking on the mouse and not having to know and write the code manually.

In this case, it is a question of adding two new columns, deleting the old ones and reordering by displacing the new columns.

These operations are much easier than writing the code directly.

[Anonymous]

This looks to me as just a matter of importing this into Power Query using the correct delimiter. Those must be headers, so I'm sure that if you choose the right delimiter, this will all line up for you. 

[Jakinta]

Here is another approach...

NextStep = Table.FromRows(List.Transform(Table.ToRows(Source),each if _{1}="No" then List.RemoveLastN(List.InsertRange(_,2,{"","",""}), 3) else _), Table.ColumnNames(Source))

[mahoneypat · Accepted solution]

Here is one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUYpMLQaShoYKvomZeQrFJUWpqSVAAb9IIBEaDCSMjIFEokN6bmJmjl5yfq5SrE60UkBqSWoRSFk+TEUSkgodJSgCKfVNLKqEqTQ1AxLJWFXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Location = _t, Address = _t, City = _t, Country = _t, Age = _t, Email = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let
recordaslist = List.RemoveMatchingItems(Record.ToList(_), {""})
in
if [Location] = "No" then Record.FromList(recordaslist, {"Name","Location", "Age", "Email"}) else Record.FromList(recordaslist, {"Name","Location", "Address", "City", "Country", "Age", "Email"})),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom", {"Name", "Location", "Address", "City", "Country", "Age", "Email"}, {"Name", "Location", "Address", "City", "Country", "Age", "Email"})
in
#"Expanded Custom"

Pat

[freelensia]

Thanks. Your code seems to work well although I did not use it on my database. I went back and seggreated my dataset from the beginning. A bit more manual work but then didn't have to do with codes.