Forum Discussion
select if
I have this table, I want to select the answer of row 15 if the answer of row 14 is "NO".
so if is "NO" I want to show a table that contain the informations of row 1
Sure thing - so what you'll want to do is...
Go to the query/table that you posted in your screenshot and click on the Advanced Editor (button in the ribbon).
In the advanced editor, the first line you see should say let and the next line should start with Source = . Copy this snippet and paste it between the two lines.
let // Imke's function // https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/ func = (MyTable as table, MyColumnName as text) => let Source = MyTable, ShiftedList = {null} & List.RemoveLastN(Table.Column(Source, MyColumnName),1), Custom1 = Table.ToColumns(Source) & {ShiftedList}, Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {"Previous Row"}) in Custom2 , documentation = [ Documentation.Name = " Table.PreviousRow ", Documentation.Description = " Superfast way to reference previous row ", Documentation.LongDescription = " Superfast way to reference previous row ", Documentation.Category = " Table ", Documentation.Source = " www.TheBIccountant.com . http://tiny.cc/hhus5y . ", Documentation.Version = " 1.0 ", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. http://tiny.cc/hhus5y . ", Documentation.Examples = {[Description = " ", Code = " ", Result = " "]}], ReplaceMetadata = Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)), // DataThen, at the end of the Advanced Editor, you should see a line that says in and then one more short line. Add a comma at the end of the last line before the in. Then replace those last two lines with this snippet.
// invoke function GetPreviousRow = func(#"Changed Type", "Column1"), SelectRowsIfPrevRowIsNo = Table.SelectRows(GetPreviousRow, each ([Previous Row] = "NO")) in SelectRowsIfPrevRowIsNoThat's it! Pls let me know if you need anything else.
5 Replies
Hello - this is how you can add a column that returns the previous row value for a column, then select only the rows where the previous row value is NO. Screensnips are shown below for the steps and a sample script is provided. Please let me know if you need help integrating this solution into your project.
Add a column with the previous row value for another column - this one shows the previous row value of Column1. (Using Imke's custom function)
Filter the table for rows where the previous value is NO.
script
let // Imke's function // https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/ func = (MyTable as table, MyColumnName as text) => let Source = MyTable, ShiftedList = {null} & List.RemoveLastN(Table.Column(Source, MyColumnName),1), Custom1 = Table.ToColumns(Source) & {ShiftedList}, Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {"Previous Row"}) in Custom2 , documentation = [ Documentation.Name = " Table.PreviousRow ", Documentation.Description = " Superfast way to reference previous row ", Documentation.LongDescription = " Superfast way to reference previous row ", Documentation.Category = " Table ", Documentation.Source = " www.TheBIccountant.com . http://tiny.cc/hhus5y . ", Documentation.Version = " 1.0 ", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. http://tiny.cc/hhus5y . ", Documentation.Examples = {[Description = " ", Code = " ", Result = " "]}], ReplaceMetadata = Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)), // Data Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUYp0DQaSfvqOSrE60UruRampeVA+kPQHCwalpqALBZQWFeSkQvgQM0Cikak5OfnlqIbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Color = _t, Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Color", type text}, {"Column1", type text}, {"Column2", type text}}), // invoke function GetPreviousRow = func(#"Changed Type", "Column1"), SelectRowsIfPrevRowIsNo = Table.SelectRows(GetPreviousRow, each ([Previous Row] = "NO")) in SelectRowsIfPrevRowIsNoI really appreciate your help, and if you can help me to integrate your solution on my project as you say I'll be thankful
Sure thing - so what you'll want to do is...
Go to the query/table that you posted in your screenshot and click on the Advanced Editor (button in the ribbon).
In the advanced editor, the first line you see should say let and the next line should start with Source = . Copy this snippet and paste it between the two lines.
let // Imke's function // https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/ func = (MyTable as table, MyColumnName as text) => let Source = MyTable, ShiftedList = {null} & List.RemoveLastN(Table.Column(Source, MyColumnName),1), Custom1 = Table.ToColumns(Source) & {ShiftedList}, Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {"Previous Row"}) in Custom2 , documentation = [ Documentation.Name = " Table.PreviousRow ", Documentation.Description = " Superfast way to reference previous row ", Documentation.LongDescription = " Superfast way to reference previous row ", Documentation.Category = " Table ", Documentation.Source = " www.TheBIccountant.com . http://tiny.cc/hhus5y . ", Documentation.Version = " 1.0 ", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. http://tiny.cc/hhus5y . ", Documentation.Examples = {[Description = " ", Code = " ", Result = " "]}], ReplaceMetadata = Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)), // DataThen, at the end of the Advanced Editor, you should see a line that says in and then one more short line. Add a comma at the end of the last line before the in. Then replace those last two lines with this snippet.
// invoke function GetPreviousRow = func(#"Changed Type", "Column1"), SelectRowsIfPrevRowIsNo = Table.SelectRows(GetPreviousRow, each ([Previous Row] = "NO")) in SelectRowsIfPrevRowIsNoThat's it! Pls let me know if you need anything else.
