Forum Discussion

Paul_W_W's avatar
Paul_W_W
Regular Visitor
5 years ago
Solved

Changing the value in a list

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

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.

     

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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

  • Paul_W_W's avatar
    Paul_W_W
    Regular Visitor
    5 years ago

    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