Search code examples
arrayspowerquerym

Array processing in Power Query

I have a table with a column array A1, A2, A3, A4,... and an index column Ind which takes the values 1,2,3,4,... I want to create a column that returns the array element using the index. I could do it with a nested if 

     if Ind = 1 then A1
else if Ind = 2 then A2
else if Ind = 3 then A3
else if Ind = 3 then A4 ...

is there a more elegant way?

Solution

  • Are A1, A2, etc. the values in column A? If so, adding a custom column with the formula each [A] will do what you want. If you need to get the value from a different row you can use {} to get the value at index, like TableName[A]{Ind + 1}. You will have to make sure that the index you are using doesn't go below 0 or greater than or equal to the number of rows. You can use an if/else statement to keep the index bounded.