Hey again
How do I REPLACE a cells value, if the cell above meets certain conditions?
Since the textfilters deletes whole rows, I need the replace funktion.
So what I need is:
If a cell is equal "Tara (t)" then replace it with "/" and the content of the cell below with "/"
So I actually wont both of them replaced.
Can you tell me that please?
Here is an example using a shifted column to access both the original cell and the cell below.
Please examine the applied steps to obtain a better idea of the algorithm.
let
//Replace Source with your own data source
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTIYlFiQoaFZpgjqGRMZhOBpMpSApKIApMTOFiYIapGZiqAJOVmOoNQQpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
//add shifted column
#"Shifted Column" =
Table.FromColumns(
Table.ToColumns(Source) &
{{null} & List.RemoveLastN(Source[Column1],1)},
type table[Original=text, Shifted=text]),
#"Replace with /" = Table.ReplaceValue(
#"Shifted Column",
each [Original],
each [Shifted],
(x,y,z) as text=> if (y="Tara (t)") or (z = "Tara (t)") then "/" else x,
{"Original"}
),
#"Remove Shifted" = Table.RemoveColumns(#"Replace with /",{"Shifted"})
in
#"Remove Shifted"
Original Data
Results
Repost:
Hi @_V_ ,
Hope you are doing well!
I have created a "Custom Function" to handle the issue. Now you can use this in power query. Find the below M-Code
(
Column_Name as list,
Existing_Value as text,
optional Replacing_Value as text,
optional New_Column_Name as text
)
as table =>
let
CN = Column_Name,
EV = Existing_Value,
RV = Replacing_Value ?? "/",
NCN = New_Column_Name ?? "Data",
List_Creation = List.InsertRange(List.RemoveLastN(CN, 1), 0, {""}),
Table_Creation = Table.FromColumns({CN} & {List_Creation}),
Replacing_Text = Table.FromList(Table.TransformRows(Table_Creation, each if List.Contains(Record.ToList(_), EV) = true then RV else [Column1])),
Output = Table.RenameColumns(Replacing_Text,{{"Column1", NCN}})
in
Output
1. Column_Name : You need to select the column for which transformation needs to done.
2. Existing_Value : In your case it is "Tara t".
3. Replacing_Value : In your case it is "/"
4. New_Column_Name: You need to provide new column name.
Note: 3 and 4 is optional.
I have checked the same and its working fine. Find the below M-Code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1YGRIYlFiQolYKYxuoApmDQDk+ZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
Output = CF_Text_Replace(Source[Data], "Tara t", "/", "Data")
in
Output
Regards,
Balakrishnan_J
Did I answer your question? If Yes,
Then mark my post as a solution and click on the Thumbs Up 👍 to give Kudos.
Remember: You can mark multiple answers as a solution...
Hi @_V_
Follow the steps below to solve this problem:
Step 1:
- Right click on table >> Go to Edit Query
Step 2: Add an Index Column
- Select Column (for example Name
- Go to Add Column >> Index Column >> Select From 0
- Select that Index column >> Go to Add Column Tab >> click on Standard >> click on subtract >> write 1 >> Ok
- Remove Index column and Rename Subtraction column to Index
- Right click on Name column >> make duplicate column of Name column and name it ( for example PreviousRow)
- Select that PreviousRow column >> Go to Add Column → Index Column → From 0 (name it PreviousIndex
Step 3: Merge Tables
- Click Home >> Merge Queries >> Merge Queries as New
- Join [Index] with [Previous Index]
- Use Left Outer Join
Step 4:
- Expand the new column → Choose only Name
- Click on Index column and apply sort ascending
STEP 5: Final Add Custom Column
Go to Add Column Tab >> click on Custom Column
Paste this Powerquery
Final Output = if Text.Trim([Name]) = "Tara (t)" then "/"
else if Text.Trim([Sample Data_.Name]) = "Tara (t)" then "/"
else [Name]
Step 6: Remove extra columns except this
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
