I have a table with 7 columns: Date, Time, Open, High, Low, Close, TickVol. In excel, if I add this formula on H4: =if(E4>D2, "BISI","") and drag to it to the end, it works. It will mark the rows with "BISI" if the conditions were met. Now I tried to emulate this on power query M Code using this:

= if [Index] > 2 and 
     Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)[Low]{[Index]-1} > 
     Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)[High]{[Index]-3} 
then 
   "BISI" 
else 
   null

it doesn't work. emulating this with VBA is easy but trying this with M Code has me stumped. I'm trying to do this in M Code because it will make my files smaller and refresh my data faster. Hope somebody can help.

I have a table with 7 columns: Date, Time, Open, High, Low, Close, TickVol. In excel, if I add this formula on H4: =if(E4>D2, "BISI","") and drag to it to the end, it works. It will mark the rows with "BISI" if the conditions were met. Now I tried to emulate this on power query M Code using this:

= if [Index] > 2 and 
     Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)[Low]{[Index]-1} > 
     Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)[High]{[Index]-3} 
then 
   "BISI" 
else 
   null

it doesn't work. emulating this with VBA is easy but trying this with M Code has me stumped. I'm trying to do this in M Code because it will make my files smaller and refresh my data faster. Hope somebody can help.

• excel
• vba
• powerquery
• emulation

• A sample screenshot of your data would be useful here. – Tim Williams Commented Nov 21, 2024 at 0:24

2 Answers 2

you can try this

= Table.AddColumn(Source,"result", each 
[a =Table.SelectRows(Source, (x)=>x[Index]=[Index]-2)[High]{0},
b= if [Low]>a then "BISI" else null,
c = try b otherwise null]
[c])

Another approach, avoiding Table.SelectRows:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Open", Currency.Type}, {"High", Currency.Type}, {"Low", Currency.Type}, {"Close", Currency.Type}, {"Volume", Int64.Type}}),
    
    
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "BISI", 
        each if [Index]<2 then null 
                else 
                    if [Low] > #"Added Index"[High]{[Index]-2} 
                        then "BISI" else null, type nullable text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"