Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
freelensia
Advocate II
Advocate II

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

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!

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

11 REPLIES 11
freelensia
Advocate II
Advocate II

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
Not applicable

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

Anonymous
Not applicable

another way, using just the user interface.

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

freelensia_0-1621181501481.png

 

 

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
Not applicable

immagine.pngok.

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.

Manually adding and removing columns?? Lol.

Anonymous
Not applicable

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.

watkinnc
Super User
Super User

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. 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Jakinta
Solution Sage
Solution Sage

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
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors