Forum Discussion

JulianaMacedo's avatar
JulianaMacedo
Icon for Helper II rankHelper II
4 years ago
Solved

Iterating over a list in Power Query

Hi everyone, I'm having difficults getting the results I'm willing...

I have a table with a colum that is a result of invoking a function and it gives a list as a result, now I need to iterate over this list and create another column that will give a list a result.

 

= Table.AddColumn(#"Renamed Columns1", "Test", each
let list = [column1]
in 
List.Transform(list, each
if List.Contains(list,0) then "X6"
else if List.Contains(list,1) then "X7"
else if List.Contains(list,2) then "X14"
else if List.Contains(list,3) then "X15"
else if List.Contains(list,4) then "X22"
else if List.Contains(list,5) then "X23"
else if List.Contains(list,6) then "X30"
else if List.Contains(list,7) then "X31"
else "")
)

 

This is what I'm doing now, the problem is that it is giving me the right number of things that should be contained in the list as the result, but it is not iterating correctly.

 

For example, is the list contained in the Column1 has 2 numbers let's say 4 and 7, the resulting list brings me two results in the new list but only takes in account the first number.

let's say the list is like this {4,7}...what I want as a result is a new list in a the new column {X22, X31}. Instead, what I'm getting is {X22,X22}.

Any idea what I'm doing wrong here?




 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this

    = Table.AddColumn(#"Renamed Columns1", "Test", each
let list = [column1]
in 
List.Transform(list, (a)=>
if a=0 then "X6"
else if a=1 then "X7"
else if a=2 then "X14"
else if a=3 then "X15"
else if a=4 then "X22"
else if a=5 then "X23"
else if a=6 then "X30"
else if a=7 then "X31"
else "")
)

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Use this

    = Table.AddColumn(#"Renamed Columns1", "Test", each
let list = [column1]
in 
List.Transform(list, (a)=>
if a=0 then "X6"
else if a=1 then "X7"
else if a=2 then "X14"
else if a=3 then "X15"
else if a=4 then "X22"
else if a=5 then "X23"
else if a=6 then "X30"
else if a=7 then "X31"
else "")
)