select if

[Hannakhattar38]

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

[jennratten]

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
    SelectRowsIfPrevRowIsNo

[Hannakhattar38]

I really appreciate your help, and if you can help me to integrate your solution on my project as you say I'll be thankful

[jennratten · Accepted solution]

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)),

    // Data 

Then, 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
    SelectRowsIfPrevRowIsNo

That's it!  Pls let me know if you need anything else.

[Hannakhattar38]

thank you so mush

[jennratten]

You're welcome!