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