is it possible change the values in two columns based on a certain value in a different column

[bleeet]

Hi,

I am new to Powerquery and while using it I came upon a certain road block. In a column in my workbook there is a certain value (let's call it "1") that appears in some of the rows in the column and in whichever row that 1 is located I need to change the value of a cell that's located in a different column but same row.

I filtered the column to only show the rows that have 1 in it. But while researching on how to make if statements in power query I noticed that I have to make two new columns to achieve this. But if I were to combine the new columns with the existing ones wouldn't that change the values in the rows of in the existing columns that were not filtered?

does anyone happen to know how I can do this?

I hope you guys can understand what I am trying to do

thanks

 

[bleeet]

to also add to this I tried using this method where I filtered for the "1" and used replace values in the two columns I want to change the values in. After that was done I was unable to unfilter the filter I used earlier to view all the rows in the table.

 

[Plexus]

Hi Bleet

Please link in a simple example of what you went. It makes it a lot easier for people to help.

 

[bleeet]

Hi Bleet

Please link in a simple example of what you went. It makes it a lot easier for people to help.

sorry about that

my table will sort of look like this

columna	columnb	columnc
null	lion	cat
1	null	null
null	bird	rex
1	null	null

<tbody>
</tbody>

am I able to find the "1"'s in columna and change the text of the cells under columnb and columnc that are in the same row as the "1"'s in columna

so basically it will look like this

columna	columnb	columnc
null	lion	cat
1	new text	new text
null	bird	rex
1	new text	new text

<tbody>
</tbody>

 

[kkardynalski]

Here is one possible solution: If the new text for Columns B and C will be the same for all rows with "1" in Column A, then you can add a new column "CustomMerged" with the formula below. Then split this column by delimiter "|" back into new Columns B and C.

= Table.AddColumn(#"Previous Step", "CustomMerged", each if [columna] = 1 then ("NewTextColB" & "|" & "NewTextColC" ) else Text.Combine({Text.From([columnb], "en-US"), Text.From([columnc], "en-US")}, "|"))

 

[bleeet]

Here is one possible solution: If the new text for Columns B and C will be the same for all rows with "1" in Column A, then you can add a new column "CustomMerged" with the formula below. Then split this column by delimiter "|" back into new Columns B and C.

= Table.AddColumn(#"Previous Step", "CustomMerged", each if [columna] = 1 then ("NewTextColB" & "|" & "NewTextColC" ) else Text.Combine({Text.From([columnb], "en-US"), Text.From([columnc], "en-US")}, "|"))

thanks for replying.

so there isn't a way to change the value of the cell in columnb and columnc from using a if statement.