cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
freelensia
Helper III
Helper III

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
Super User IV
Super User IV

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
Helper III
Helper III

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.

Rocco_sprmnt21
Super User II
Super User II

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

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

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.

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
Solution Specialist
Solution Specialist

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
Responsive Resident
Responsive Resident

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
Super User IV
Super User IV

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

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors