Power Query- Nested functions and IF statements

Question

I need to add a column to a nested table with values based on a condition. Each field in the new column "Hybrid Value" should get the value of field "AlutMufchetetColumn", if the value of ID equals "Meyuadot"

This is the code, I think I still can't get my head around the passed on variables and scopes.

You can see the numbers under Hybrid Value, which are taken from Column Alutmufchetet, only when the values of ID include the string "AlutMuf"

ShoviHibrid=

      let
         X= Table.FromRecords(Table.TransformRows(IDandKvutsa, (r)=>

         Record.TransformFields(r, {"CSV Table With ID and Kvutsa", 
         (TableField)=>

        Table.AddColumn([CSV Table With ID and Kvutsa],{"Hybrid Value",              

              if Text.Contains(TableField[ID],"Meyuadot" ) then

                        "Success" else "not success"  })

               })))

     in

         X

@horseyride see my edited post. Let me know if I need to provide more details. — POL
– POL, Commented Aug 20, 2024 at 20:42
I edited the code too, to remove unnecessary steps. I will add them later. — POL
– POL, Commented Aug 20, 2024 at 21:07
IDandKvutsa is the main table. In column "CSV Table With ID and Kvutsa" I have the nested tables. What I'm trying to do, is transforming the fields in "CSV Table With ID and Kvutsa" to add columns to the nested table. — POL
– POL, Commented Aug 21, 2024 at 5:03
The table above is the nested one. The first half of the code deals with fetching the filed with the table, the second half adds a column to the nested table. I think I miss something about records transformation and passing variable through the functions — POL
– POL, Commented Aug 21, 2024 at 14:40

POL · Accepted Answer · 2024-08-21 21:06:04Z

0

I figured it out, my main mistake (among others) was the {} in the AddColumn function. This is how it should look like:

   ShoviHibrid=

      let
         X= Table.FromRecords(Table.TransformRows(IDandKvutsa, (r)=>

         Record.TransformFields(r, {"CSV Table With ID and Kvutsa", 
         (TableField)=>

        Table.AddColumn(TableField,"Hybrid Value", (SubRecord)=>             

              if Text.Contains(SubRecord[ID],"Meyuadot") then

                        "Success" else "not success"

              )})))

     in

         X

answered Aug 21, 2024 at 21:06

POL

657 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Power Query- Nested functions and IF statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related