Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
See picture below. There is one row (highlighted) that the value is wrong. Is there anyway I can remove this row via formula?
Basically if the values of the row above and below are equal to each other but different to a cell inbetween, remove that row.
Thanks for any input.
Solved! Go to Solution.
Here is a function you can use to add the next and previous row values as columns to your table.
You would invoke it like:
Table_Window(MyQuery, {-1, 1}, {"id})
This will add "id-1" and "id+1" columns
// Table_Window
let fn = (table as table, offsets as anynonnull, columns as anynonnull) =>
let columns = if not (columns is list) then {columns} else columns in
let indexColumnName = Text.NewGuid() in
let table = Table.AddIndexColumn(table, indexColumnName, 0) in
let withIndexFn = (table as table, offset as anynonnull) =>
if offset = {} then
table
else if offset is list then
@withIndexFn(@withIndexFn(table, offset{0}), List.Skip(offset))
else
let indexColumnName2 = Text.NewGuid() in
let table2 = Table.SelectColumns(table, columns) in
let table2 = Table.AddIndexColumn(table2, indexColumnName2, -offset) in
let newColumnNames = List.Transform(columns, each _ & (if offset > 0 then "+" else "") & Number.ToText(offset)) in
let table2 = Table.RenameColumns(table2, List.Zip({columns, newColumnNames})) in
let joinedTable = Table.Join(table, {indexColumnName}, table2, {indexColumnName2}, JoinKind.LeftOuter) in
let joinedTable = Table.RemoveColumns(joinedTable, {indexColumnName2}) in
let joinedTable = Table.Sort(joinedTable, indexColumnName) in
joinedTable
in
let table = withIndexFn(table, offsets) in
let table = Table.RemoveColumns(table, {indexColumnName}) in
table
in
fn
If you dont want to make a custom function then you can try the below M Code also it will also work if the data increases in future.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwNDE0NTM1MTJWitUZSL4JnfmmFPLN6Mw3p7J+UvkWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Pvalue", each try #"Added Index" [Data] {[Index] - 1} otherwise 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Nvalue", each try #"Added Index" [Data] {[Index] + 1} otherwise 0),
#"Added Custom2" = Table.RemoveColumns(Table.SelectRows(Table.AddColumn(#"Added Custom1", "Result", each if [Pvalue] = [Nvalue] and [Pvalue] <> [Data] then 0 else [Data]),each ([Result] <> 0)),{"Index", "Data", "Pvalue", "Nvalue"})
in
#"Added Custom2"
Here is a function you can use to add the next and previous row values as columns to your table.
You would invoke it like:
Table_Window(MyQuery, {-1, 1}, {"id})
This will add "id-1" and "id+1" columns
// Table_Window
let fn = (table as table, offsets as anynonnull, columns as anynonnull) =>
let columns = if not (columns is list) then {columns} else columns in
let indexColumnName = Text.NewGuid() in
let table = Table.AddIndexColumn(table, indexColumnName, 0) in
let withIndexFn = (table as table, offset as anynonnull) =>
if offset = {} then
table
else if offset is list then
@withIndexFn(@withIndexFn(table, offset{0}), List.Skip(offset))
else
let indexColumnName2 = Text.NewGuid() in
let table2 = Table.SelectColumns(table, columns) in
let table2 = Table.AddIndexColumn(table2, indexColumnName2, -offset) in
let newColumnNames = List.Transform(columns, each _ & (if offset > 0 then "+" else "") & Number.ToText(offset)) in
let table2 = Table.RenameColumns(table2, List.Zip({columns, newColumnNames})) in
let joinedTable = Table.Join(table, {indexColumnName}, table2, {indexColumnName2}, JoinKind.LeftOuter) in
let joinedTable = Table.RemoveColumns(joinedTable, {indexColumnName2}) in
let joinedTable = Table.Sort(joinedTable, indexColumnName) in
joinedTable
in
let table = withIndexFn(table, offsets) in
let table = Table.RemoveColumns(table, {indexColumnName}) in
table
in
fn
thank you, kindly.
Check out the July 2025 Power BI update to learn about new features.