Forum Discussion
how to dynamically replace values for multiple columns
does the statemap query need to be text or number data type? my "mt" columns are text type.
Hello vc25,
Good question. StateMap[Code] should be Text, since your mt columns are also Text.
In the solution, this line is the key: Record.FieldOrDefault(MapRecord, Text.From(_), _)
Because Text.From(_) is used, the lookup is done using text values. That’s why the mapping table is explicitly converted here:
MapRecord = Record.FromTable(
Table.TransformColumnTypes(StateMap, {{"Code", type text}})
)
As long as both the mt column values and StateMap[Code] are text, the replacement will work correctly and scale as columns are added or removed.
Microsoft docs (reference):
Table.TransformColumns
https://learn.microsoft.com/powerquery-m/table-transformcolumnsRecord.FromTable
https://learn.microsoft.com/powerquery-m/record-fromtableRecord.FieldOrDefault
https://learn.microsoft.com/powerquery-m/record-fieldordefault
Hope that clarifies it
I keep getting this error.
mapping table called "fishnames"