Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Paul_W_W
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
Jimmy801
Community Champion
Community Champion

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
Paul_W_W
Regular Visitor

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
Community Champion
Community Champion

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
Community Support
Community Support

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:

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.

 

Anonymous
Not applicable

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
Not applicable

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors