I frequently use a handy function (from Chris Webb's site as I recall) to translate data codes from source data into human-readable values. Like many people I get data from ERP and OLTP sources with inscrutable identifiers.
The translation values are hard-coded in the function. So if my data has "N" and I want the output column to return "North"
And it's called within the primary query by
AddNewDirectionCol = Table.AddColumn(RenameOldDirection, "Direction", each fnTranslateCompass([CompassPoint]), type text)
I can have 30+ rows for the translation values, with separate translation tables for separate queries. These values change more often than I'd like. I thought that if I maintain the data in a local table within the spreadsheet I can modify the spreadsheet data once and the queries will pick it up without having to adjust each query...
This works fine as a generic function, but of course it's going to load the translation table for every line of the source data where the translation is happening. That will push the query time into next week for million+ data sources.
Is there a way to call the translation table just once so that the function within the query can re-use the table for every line of the primary query? Or am I stuck with hard-coding each separate translation function?
The translation values are hard-coded in the function. So if my data has "N" and I want the output column to return "North"
Code:
(Lookup as text) =>
let
values = {
{"N", "North"},
{"E", "East"},
{"S", "South"},
{"W", "West"},
{Lookup, "Other"}
},
Result = List.First(List.Select(values, each _{0}=Lookup)){1}
in
Result
And it's called within the primary query by
AddNewDirectionCol = Table.AddColumn(RenameOldDirection, "Direction", each fnTranslateCompass([CompassPoint]), type text)
I can have 30+ rows for the translation values, with separate translation tables for separate queries. These values change more often than I'd like. I thought that if I maintain the data in a local table within the spreadsheet I can modify the spreadsheet data once and the queries will pick it up without having to adjust each query...
Code:
(SourceTable as text, LookupCode as text) =>
let
// Get the translation table
Source = Excel.CurrentWorkbook(){[Name=SourceTable]}[Content],
// Rename the columns in case they aren't standard
RenameCols = Table.RenameColumns(Source,{{Table.ColumnNames(Source){0}, "Index"}, {Table.ColumnNames(Source){1}, "Value"}}),
Result = List.First(Table.Column(Table.SelectRows(RenameCols, each [Index] = LookupCode),"Value"))
in
Result
Is there a way to call the translation table just once so that the function within the query can re-use the table for every line of the primary query? Or am I stuck with hard-coding each separate translation function?