powerquery

Transformations in one query based on list of column names and required transformation - in another table


I want to make changes to some of the columns in my query MAIN. Here is some sample data (all columns are numerical):

ID  | col1  | col2 | cola  | colb
1   |  1    |  17  |   3   |  10
2   |  3    |  6   |   5   |   5

Another query called CHANGES has a list of the required changes (all text columns):

varnm | cndvr| cndvl 
 col1 | col2 | 16,17,18,19   
 cola | colb | 5  

The change I am trying to do - for every row in table CHANGES: find the columns named in varnm and cndvr - for every row check if the value in the cndvr column exists among the list of values in cndvl, the value in the varnm column remains unchaged. If, on the other hand, the value in the cndvr column does NOT exists among the list of values in cndvl, the value in the varnm column is changed to 9999. So the expected result in MAIN look like this:

ID  | col1  | col2 | cola  | colb
1   |  1    |  17  | 9999  |  10
2   | 9999  |  6   |   5   |   5

I tried to do it by creating a function called REPFUNC:

(row as record, dt as table) as table =>
    let

        // Extract condition values
        varnm = Record.Field(row, "varnm"), // Column to transform
        cndvr = Record.Field(row, "cndvr"), // Column for condition
        cndvl = Text.Split(Record.Field(row, "cndvl"), ","),
    
        // Transform data based on the condition
        TransformedData = Table.ReplaceValue(
                    dt,
                    each varnm, 
                    each if List.Contains(cndvl, Text.From(cndvr)) then varnm else 999999, 
                    Replacer.ReplaceValue, 
                    {varnm}
                )
    in
        TransformedData

I call the function in my MAIN query like this:

let
    Source = ......,
    // Process each row in CHANGES
    ProcessedData = List.Accumulate(
        Table.ToRecords(CHANGES),
        Source,
        (state, currentRow) => Function.Invoke(REPFUNC, {currentRow, state})
    )
in
    ProcessedData

The result: NOTHING CHANGES.

ADDITIONALLY: There is a great suggestion by @AlexisOlson in a similar problem here to simplify things by restructuring. I would unpivot the columns in varnm, merge CHANGES to MAIN, create a simple transformation step and pivot back. The thing is some transformation in the original problem are stepped - so the same column may appear, more than once, in both varnm AND in cndvr - which seems to kill the unpivot option.

EDIT - adding code to recreate the example: the MAIN data:

let
    Source = Table.FromRows(
        {{1, 1, 17, 3, 10},{2, 3, 6, 5, 5}},
        {"ID", "col1", "col2", "cola", "colb"})
in
    Source

The CHANGES table:

let
    Source = Table.FromRows(
        {{"col1", "col2", "16,17,18,19"},{"cola", "colb", "5"}},
        {"varnm", "cndvr", "cndvl"})
in
    Source

Solution

  • Here's one way, using the Table.TransformRows function with a Record.TransformFields argument:

    REPFUNC

    (r as record) => 
    
    List.Accumulate(
        Table.ToRecords(CHANGES),
            r,
            (s,c)=> Record.TransformFields(s, {c[varnm], each 
            if List.Contains(Text.Split(c[cndvl],","), 
                Text.From(Record.Field(r,c[cndvr])) )  
            then _ 
            else 9999}))
    

    MAIN

    let
        Source = Table.FromRecords(
                    {[ID=1,col1=1,col2=17,cola=3,colb=10],
                     [ID=2, col1=3,col2=6,cola=5,colb=5]},
                    type table[ID=Int64.Type,col1=Int64.Type,col2=Int64.Type,cola=Int64.Type,colb=Int64.Type]),
        
        Transforms = Table.FromRecords(Table.TransformRows(Source, each REPFUNC(_))),
        
        Types = Table.TransformColumnTypes(Transforms, List.Transform(Table.ColumnNames(Transforms), each {_, Int64.Type}))
    in
        Types
    

    CHANGES

    let
        Source = Table.FromRecords({
            [varnm="col1",cndvr="col2",cndvl="16,17,18,19"],
            [varnm="cola", cndvr="colb",cndvl="5"]},
            type table[varnm=text, cndvr=text,cndvl=text])
    in
        Source
    

    Source
    enter image description here

    CHANGES
    enter image description here

    Results
    enter image description here

    Edit to show issue with your approach: Your approach fails because in the Table.Replace function within REPFUNC your 2nd and 3rd arguments are not referring to the table in the first argument.
    To use that function within your original code, try:

    (row as record, dt as table) as table =>
        let
    
            // Extract condition values
            varnm = Record.Field(row, "varnm"), // Column to transform
            cndvr = Record.Field(row, "cndvr"), // Column for condition
            cndvl = Text.Split(Record.Field(row, "cndvl"), ","),
        
            // Transform data based on the condition
            TransformedData = Table.ReplaceValue(
                        dt,
                        each Record.Field(_,cndvr),
                        "999999", 
                        (x,y,z) => if List.Contains(cndvl, Text.From(y)) then x else z,
                        {varnm}
                    )
        in
            TransformedData