Convert DAX prefix-based hierarchy lookup to Power Query custom column

[Nazdac2024]

I have the following DAX expression in a report that extracts the level 1 category name based on prefix-matching and a known hierarchy level:

Level_1 = 
VAR ThisCode = 'DimensionTable'[Code]
RETURN
CALCULATE (
    MAX('DimensionTable'[Name]),
    FILTER (
        'DimensionTable',
        'DimensionTable'[Level] = 1 &&
        LEFT(ThisCode, LEN('DimensionTable'[Code])) = 'DimensionTable'[Code]
    )
)

I want to implement the same logic directly in Power Query as a custom column, using M language. The logic should:

• Use the first characters of a code field

• Match it against rows where Level = 1

• Return the corresponding Name from that row

  

Could someone help me rewrite this logic correctly in Power Query?

[johnt75 · Accepted solution]

You can try

= let 
    CurrentCode = [iec_code],
    Name =
    List.First(
        Table.Column(
            Table.SelectRows( #"Previous Step", each Text.StartsWith([iec_code], CurrentCode) and [level] = 1 ),
            "name"
        )
    )
in
    Name