Power query losing reference to the parent record after updating child/nested record

user_2512953 0
Viewed 27

I have the following record:

Source = [
        Properties = [
            steps = {
            [
                index = {
                        [
                            display = "Something",
                            data = "data2"
                        ]
                    },
                access = "Column1"
            ],
            [
                indices = {
                        [
                            display = "Something else",
                            data = "data2"
                        ]
                    },
                access = "Column2"
            ]   
                    
            },
            
            opt = [
                    key1 = "val1",
                    key2 = "val2",
                    key3 = "val3"
                ]
            ]
        ]

I am updating/adding a new field inside the nested record. However, after that I am unable to get the reference to the original record with the modified field.

Here is the M code for the change. I am adding a new field updatedKey3 which contained the capitalized key3 field and then I am removing the key3.

let Source = [
    Properties = [
        steps = {
        [
            index = {
                    [
                        display = "Something",
                        data = "data2"
                    ]
                },
            access = "Column1"
        ],
        [
            indices = {
                    [
                        display = "Something else",
                        data = "data2"
                    ]
                },
            access = "Column2"
        ]   
                
        },
        
        opt = [
                key1 = "val1",
                key2 = "val2",
                key3 = "val3"
            ]
        ]
    ],

    step1 = Source[Properties][opt],
    step2 = Record.AddField(step1, "updatedKey3", Text.Upper(step1[key3])),
    step3 = Record.RemoveFields(step2, {"key3"})
in 
    step3

step3 returns the record:

[key1 = "val1", key2 = "val2", updatedKey3 = "VAL3"]

However I want this change within the Source record and view it completely. Any help would be appreciated.

m powerbi powerquery
1 Answers

I was able to do it by adding, removing and renaming fields. The following is the updated code for it. If anyone has a more optimized way to do so, please let me know.

 let Source = [
      Properties = [
            steps = {
            [
                index = {
                        [
                            display = "Something",
                            data = "data2"
                        ]
                    },
                access = "Column1"
            ],
            [
                indices = {
                        [
                            display = "Something else",
                            data = "data2"
                        ]
                    },
                access = "Column2"
            ]   
                    
            },
            
            opt = [
                    key1 = "val1",
                    key2 = "val2",
                    key3 = "val3"
                ]
            ]
        ],
    
        step1 = Source,
        step2 = Record.RenameFields(Record.RemoveFields(Record.AddField(step1[Properties][opt], "tmpKey", Text.Upper(step1[Properties][opt][key3])), "key3"), {"tmpKey", "key3"}),
        step3 = Record.RenameFields(Record.RemoveFields(Record.AddField(step1[Properties], "tmpOpt", step2), "opt"), {"tmpOpt", "opt"}),
        step4 = Record.RenameFields(Record.RemoveFields(Record.AddField(step1, "tmpProperties", step3), "Properties"), {"tmpProperties", "Properties"})
in
    step4
user_25129530
Related