CODEWITHSUNDEEP
excelexcel-formulapowerquery
SanderJ
SanderJ

Reputation: 55

Convert Excel-Formula to PowerQuery Column Formula - Based on Index and a condition

I am trying to convert an Excel formula to an Excel PowerQuery "Column" Formula. The formula in question is: =IF([@Column2]>2,INDEX([Column1],XMATCH(2,FILTER([Column2],ROW([Column2])<ROW()),0,-1)),[@Column1])

Please see the image below for the example/goal, which is achieved by the formula above.

Example of Goal

I am struggling to figure out how to do this in PowerQuery, my reasons for trying are a possible increase in performance and ease of use.

AI suggested the following formula, which does not work because "Index" is not found. I'm not too sure if it's a good basis or if AI sent me down the wrong rabbit hole.

if [Column2] > 2 then 
Table.LastN(#table({[Column2], [Column1]}), 1,{ #"Index" = List.PositionOf( #"Column2", 2 ), #"Other" = [Column2] }) 
    {0}[Column1] else [Column1]

Any suggestions are welcome.

Upvotes: 1

Views: 36

Answers (1)

horseyride
horseyride

Reputation: 21298

It looks like you want the value from the first column when the second column =2

so in PowerQuery, you could add column ... custom column ...

= if [Column2] = 2 or [Column2]="2" then [Column1] else null

then right click and fill down that new column

To pick up the first row you could add column ... index column...

then add column ... custom column ...

=  if [Index] = 0 then [Column1] else if [Column2] = 2 or [Column2]="2" then [Column1] else null

then right click and fill down that new column

full sample code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Column3", each if [Index] = 0 then [Column1] else if [Column2] = 2 or [Column2]="2" then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Column3"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"})
in  #"Removed Columns"

Upvotes: 1

Related Questions