cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

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

View solution in original post

5 REPLIES 5
Highlighted
Memorable Member
Memorable Member

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)

 

 

 

Highlighted
Community Support
Community Support

Hi, @Paul_W_W 

 

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

Raw data:

c1.png


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:

c2.png

 

Best Regard

Allan

 

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

 

Highlighted
Super User II
Super User II

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

View solution in original post

Highlighted
Regular Visitor

Firstly thank you to @Rocco_sprmnt21 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

Highlighted

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

 

 

 

image.png

 

PS

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors