Error function as any

[ffirda99]

let
// Dapatkan nama kolom dari tabel
ColumnNames = Table.ColumnNames(#"(5) Notes PM Machine"),
// Dapatkan indeks dari kolom yang mengandung kata kunci
GetBacklashValue = (row as record) as any =>
let
// Cari kolom yang mengandung kata kunci
BacklashColumnIndex = List.PositionOfAny(ColumnNames, {"Cek Backlash X Axis", "Check measure the backlash X Axis", "Cek Backlast X Axis"}, Comparer.OrdinalIgnoreCase),
// Jika ditemukan, ambil kolom ke-3 setelahnya
TargetColumnIndex = if BacklashColumnIndex <> -1 and BacklashColumnIndex + 3 < List.Count(ColumnNames)
then BacklashColumnIndex + 3
else null,
// Ambil nilai dari kolom yang ditemukan
Value = if TargetColumnIndex <> null then Record.Field(row, ColumnNames{TargetColumnIndex}) else null
in
Value
in
each GetBacklashValue(_)

 Hi, I have that formula in my Power Query. I write on:

but the result is: 

I want something like this:

I have more than 30 columns in 1 table, I want to create a new column in that table by retrieving data based on rules like this:

if I have the value "Check Backlash X Axis" in column 2, then the result in this new column is 7micron (which is located in column 5).
if I have the value "Check measure the backlash X Axis" in column 6, then the result in this new column is 97microns (which is located in column 9).

the values ​​of "Check Backlash X Axis", "Check measure the backlash X Axis", and "Check Backlash X Axis" are in various columns (for example from columns 1-30)

Is something wrong with my formula? help me please. Thankyou!

[Anonymous · Accepted solution]

Hi @ffirda99 

Additionally, here is my solution for this question. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0BBMKYY4+oa5AJjqK1YlWMoJznZzABBbVIHXGaJqdncEEimqQOhMs9ugoubiACajq2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Column2 = _t, Column1 = _t, Column3 = _t, Column6 = _t, Column4 = _t, Column7 = _t, Column5 = _t, Column8 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    
    // index positions of columns where you want to check the values
    CheckPositions = { 1, 3, 5},
    // values you want to check in the previous columns
    CheckList = {"AA", "BB", "CC"},
    // index positions of result values for the new column
    ResultPositions = { 2, 4, 6},
    // add a custom column
    Custom2 = Table.AddColumn(#"Changed Type", "new", each let rowValues = Record.FieldValues(_), checkValues = List.Transform(CheckPositions, each rowValues{ _ }) in try rowValues{ResultPositions{List.PositionOfAny(checkValues, CheckList)}} otherwise null)
in
    Custom2

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Anonymous]

Hi @ffirda99 

Your original formula generates a custom function. You can create a blank query and add your function code to its Advanced Editor. Give the query a function name. In below image, I name it as "myFunction".

Then add a custom column and use myFunction(_) to invoke above function. If your formula is correct, you should have the expected outcome in the new column. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!