Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Icon for Helper IV rankHelper IV
1 year ago
Solved

Replace values in one column with another column value with condition across multiple columns

Hi,   I am asking for help to see if there is a better solution to the my current code   I am needing to replace a value in a column with a value in another column based on a condition but across...
Need Help
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    Try code below. I used the Table.TransformRows() to loop through all rows in the table and if the AllocationId of the row is <> null and "" ,  I use List.Accumulate() to do a Record.TransformFields, for each pair. 

    let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "lZNBC4IwFID/y84e3JxixyVqUcNWkal4UCSDOnTp/6dNYW6z3OANBu/7eG97KwpQuRFz7O8K43tDSQwsAO1uGwOJB1BaEyaC+5wSMaMP9xfRsoNKwJVwqOyAZ+MgSesXJRtVkg0SiEULMrRc1moppo4KUhJIDmzm8NLr0I0r3wsysGSR2s3EuqQd9qRkJ7/IGP5CyRFxia4ab5EjTNiNkqTLddCMSIO0HMG6dC1yPr05oozPPNI8+C/RPpge6edMnAn/H5FvJcJTiPID",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let _t = ((type nullable text) meta [Serialized.Text = true]) 
        in type table [
            Id = _t, 
            Working_Amount__c = _t, 
            Standby_Onsite_Amount__c = _t, 
            Standby_Offsite_Amount__c = _t, 
            Working_Amount_s__c = _t, 
            Onsite_Standby_Amount_s__c = _t, 
            Offsite_Standby_Amount_s__c = _t, 
            AllocationId = _t
        ]
    ),
    ChangedType = Table.TransformColumnTypes(
        Source,
        {
            {"Id", type text}, 
            {"Working_Amount__c", Int64.Type}, 
            {"Standby_Onsite_Amount__c", Int64.Type}, 
            {"Standby_Offsite_Amount__c", Int64.Type}, 
            {"Working_Amount_s__c", Int64.Type}, 
            {"Onsite_Standby_Amount_s__c", Int64.Type}, 
            {"Offsite_Standby_Amount_s__c", Int64.Type}
        }
    ),
    // Define the column pairs for replacement
    ColumnPairs = {
        {"Working_Amount__c", "Working_Amount_s__c"},
        {"Standby_Onsite_Amount__c", "Onsite_Standby_Amount_s__c"},
        {"Standby_Offsite_Amount__c", "Offsite_Standby_Amount_s__c"}
    },
    UpdatedTable = Table.FromRecords(
        Table.TransformRows(ChangedType,
            (row) =>
                if (row[AllocationId] ?? "") <> "" then
                    List.Accumulate(
                        ColumnPairs,
                        row,
                        (State, CurrentPair) =>
                            Record.TransformFields(State, {CurrentPair{0}, each Record.Field(State, CurrentPair{1})})
                    )
                else
                    row
        )
    )
in
    UpdatedTable

     

Omid_Motamedise's avatar
Omid_Motamedise
Icon for Super User rankSuper User
1 year ago

You can also use this

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZNBC4IwFID/y84e3JxixyVqUcNWkal4UCSDOnTp/6dNYW6z3OANBu/7eG97KwpQuRFz7O8K43tDSQwsAO1uGwOJB1BaEyaC+5wSMaMP9xfRsoNKwJVwqOyAZ+MgSesXJRtVkg0SiEULMrRc1moppo4KUhJIDmzm8NLr0I0r3wsysGSR2s3EuqQd9qRkJ7/IGP5CyRFxia4ab5EjTNiNkqTLddCMSIO0HMG6dC1yPr05oozPPNI8+C/RPpge6edMnAn/H5FvJcJTiPID", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Working_Amount__c = _t, Standby_Onsite_Amount__c = _t, Standby_Offsite_Amount__c = _t, Working_Amount_s__c = _t, Onsite_Standby_Amount_s__c = _t, Offsite_Standby_Amount_s__c = _t, AllocationId = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Working_Amount__c", Int64.Type}, {"Standby_Onsite_Amount__c", Int64.Type}, {"Standby_Offsite_Amount__c", Int64.Type}, {"Working_Amount_s__c", Int64.Type}, {"Onsite_Standby_Amount_s__c", Int64.Type}, {"Offsite_Standby_Amount_s__c", Int64.Type}}),

ColumnPairs = {
{"Working_Amount__c", "Working_Amount_s__c"},
{"Standby_Onsite_Amount__c", "Onsite_Standby_Amount_s__c"},
{"Standby_Offsite_Amount__c", "Offsite_Standby_Amount_s__c"}},
X=List.Accumulate(ColumnPairs,ChangedType,(a,b)=>Table.ReplaceValue(a,
each Record.Field(_,b{0}),
each if [AllocationId] <> null or [AllocationId] = "" then Record.Field(_,b{1})
else Record.Field(_,b{0}),
Replacer.ReplaceValue,{b{0}}))

in
X

 

 

  • Richard_Halsall's avatar
    Richard_Halsall
    Icon for Helper IV rankHelper IV
    1 year ago

    Hi Omid_Motamedise , thanks for the reply unfortunately the values are changing even if the allocation id column is blank or null, they are to change only if they are populated.

     

    • PwerQueryKees's avatar
      PwerQueryKees
      Icon for Super User rankSuper User
      1 year ago

      Try code below. I used the Table.TransformRows() to loop through all rows in the table and if the AllocationId of the row is <> null and "" ,  I use List.Accumulate() to do a Record.TransformFields, for each pair. 

      let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "lZNBC4IwFID/y84e3JxixyVqUcNWkal4UCSDOnTp/6dNYW6z3OANBu/7eG97KwpQuRFz7O8K43tDSQwsAO1uGwOJB1BaEyaC+5wSMaMP9xfRsoNKwJVwqOyAZ+MgSesXJRtVkg0SiEULMrRc1moppo4KUhJIDmzm8NLr0I0r3wsysGSR2s3EuqQd9qRkJ7/IGP5CyRFxia4ab5EjTNiNkqTLddCMSIO0HMG6dC1yPr05oozPPNI8+C/RPpge6edMnAn/H5FvJcJTiPID",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let _t = ((type nullable text) meta [Serialized.Text = true]) 
        in type table [
            Id = _t, 
            Working_Amount__c = _t, 
            Standby_Onsite_Amount__c = _t, 
            Standby_Offsite_Amount__c = _t, 
            Working_Amount_s__c = _t, 
            Onsite_Standby_Amount_s__c = _t, 
            Offsite_Standby_Amount_s__c = _t, 
            AllocationId = _t
        ]
    ),
    ChangedType = Table.TransformColumnTypes(
        Source,
        {
            {"Id", type text}, 
            {"Working_Amount__c", Int64.Type}, 
            {"Standby_Onsite_Amount__c", Int64.Type}, 
            {"Standby_Offsite_Amount__c", Int64.Type}, 
            {"Working_Amount_s__c", Int64.Type}, 
            {"Onsite_Standby_Amount_s__c", Int64.Type}, 
            {"Offsite_Standby_Amount_s__c", Int64.Type}
        }
    ),
    // Define the column pairs for replacement
    ColumnPairs = {
        {"Working_Amount__c", "Working_Amount_s__c"},
        {"Standby_Onsite_Amount__c", "Onsite_Standby_Amount_s__c"},
        {"Standby_Offsite_Amount__c", "Offsite_Standby_Amount_s__c"}
    },
    UpdatedTable = Table.FromRecords(
        Table.TransformRows(ChangedType,
            (row) =>
                if (row[AllocationId] ?? "") <> "" then
                    List.Accumulate(
                        ColumnPairs,
                        row,
                        (State, CurrentPair) =>
                            Record.TransformFields(State, {CurrentPair{0}, each Record.Field(State, CurrentPair{1})})
                    )
                else
                    row
        )
    )
in
    UpdatedTable