Changing the value in a list

[Paul_W_W]

Hello All

I want to create a two dimension List array (eg Lookup = {{1..10},{1..10}} and then be able to change a specific entry.

I have tried Lookup{1}{4} = 99 but PQ does not like it. I cannot use List.Replace because there are duplicate values.

Does anyone have any suggestions?

Thx Paul

[Paul_W_W]

Firstly thank you to @Anonymous and @v-alq-msft for a speedy response and pointing me in the right direction.

However @Jimmy801 picked up on what I discovered myself which was if there are duplicate values in the list it all falls apart.

@Jimmy801 I have tested your solution and it hit the nail on the head, I just need to read your code so that I can fully understand it.

Many thanks to one and all

Paul

[Jimmy801 · Accepted solution]

Hello @Paul_W_W 

as List.PositionOf is not working, because if yuo have double entries, it won't work. So the only way is to use List.Positions and List.Zip to add a position number to every entry and then use List.Tranform to change your required entry. Here a function that does that for you. This function takes a list, firstlevel, second level and a new entry. Check it out and give feedback

//fxChangeEntry2LevelList
(List as list, Level1 as number, Level2 as number, NewEntry as any)=>
let


    Source = List,
    TransformFirstLevel = List.Positions(Source),
    TransformSecondLevel = List.Transform(List.Buffer(Source),each List.Zip({_, List.Positions(_)})),
    Zip = List.Zip({TransformSecondLevel,TransformFirstLevel}), 
    Transform = List.Transform
    (
        Zip,
        (listlevel1)=> if listlevel1{1}= Level1 then 
        List.Transform
        (
            listlevel1{0},
            (listlevel2)=> if listlevel2{1}= Level2 then NewEntry else listlevel2{0}
           
        )

        else
        List.Transform
        (
            listlevel1{0},
            each _{0}
        )
    )
in
    Transform

Copy paste this code to the advanced editor in a new blank query. Give the function a name and try to apply ti

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[v-alq-msft]

Hi, @Paul_W_W 

As what is suggested by @Anonymous , I created data to reproduce your scenario. The pbix file is attached in the end.

Raw data:

You may add a new step as below to replace the value of fourth index for the second list.

= List.Transform(
   Source,
   each if
   List.PositionOf(Source,_)=1
   then List.ReplaceRange(
       _,
       4,1,{99}
   )
   else _
)

Result:

Best Regard

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I suggest you read the this blog   here  especially the immutable paragraph.

you want to have a list equal to the original one, but with only some  values changed, you have to somehow build a copy and change only the relevant values.

Un modo per fare questo è l'uso della funzione list.Transform, ad esempio, in questo modo:

List.Transform( Lookup, (oldValue) => if condition  then newValue else oldValue)

[Anonymous]

In effetti c'è un modo un po' più diretto di quanto pensassi di ottenere lo stesso risultato. Ed è il seguente:

let
    replaceMatrixElement = (list, row, col,value)=> List.ReplaceRange(list,col,1, {List.ReplaceRange(list{col},row,1,{value})})
in
    replaceMatrixElement

PS

nella batteria di funzioni libreria https://docs.microsoft.com/it-it/powerquery-m/list-replacerange, si trova spesso quello che serve.