Custom function to convert from Euro to Dollar

[Mic1979]

Dear all,

I need to create a custom function to convert prices from Euro to Dollar.

I tried with this but it doesn't work:

let
CONVERSION_FROM_EURO_TO_DOLLAR = (Input_Table as table, Input_Column as number, CONVERSION_RATE as number) =>
Table.ReplaceValue (
Input_Table,
each [#"Input_Column"],
[#"Input_Column"]*CONVERSION_RATE,
Replacer.ReplaceValue,
{"Input_Column"})

in CONVERSION_FROM_EURO_TO_DOLLAR

I am invoking the function in this way:

#"Removed HELPER DIRECT_COST_AND_COGS" = Table.RemoveColumns(#"Changed Type3",{"Custom"})

#"MATERIAL_DIRECT_COST_AND_ADDERS_$"= CONVERSION_FROM_EURO_TO_DOLLAR(

     #"Removed HELPER DIRECT_COST_AND_COGS",

     "MATERIAL_DIRECT_COST_AND_ADDERS_€",

     EXCHANGE_PARAMENTER)

I think the way how I am passing the parameters to the function is not correct.

Thanks for your help in advance.

[ZhangKun · Accepted solution]

First of all, it is not recommended that you use the Table.ReplaceValue function because it is more complicated, but for the purpose of teaching, let's first use it to solve this problem:

(Input_Table as table, Input_Column_Name as text, CONVERSION_RATE as number) =>
Table.ReplaceValue (
    Input_Table,
    null,
    null,
    (val, old, new) => val * CONVERSION_RATE,
    {Input_Column_Name}
)

/*
Function definition:
Table.ReplaceValue(
    table as table, 
    oldValue as any, 
    newValue as any, 
    replacer as function, 
    columnsToSearch as list
) as table


The fourth parameter of this function is a function with three parameters. For the convenience of description, let's assume that this function is called fx. The three parameters of the fx function are:
1. Each value of the column specified in the fifth parameter of Table.ReplaceValue
2. The old value
3. The new value

For your question, we only need to multiply each value of the specified column by CONVERSION_RATE, so there is no need to specify the second and third parameters of Table.ReplaceValue (specified as null).
*/

In fact, a more understandable way is to use the Table.TransformColumns function:

(Input_Table as table, Input_Column_Name as text, CONVERSION_RATE as number) =>
Table.TransformColumns(
    Input_Table, 
    {Input_Column_Name, each _ * CONVERSION_RATE}
    //{Input_Column_Name, each _ * CONVERSION_RATE, Currency.Type}
)

/*
Function definition:
Table.TransformColumns(
    table as table, 
    transformOperations as list, 
    optional defaultTransformation as nullable function, 
    optional missingField as nullable number
) as table

This function takes the first two parameters, and the second parameter (transformOperations) is a list, which can be in the following three forms:
1 {column name, transformation function}
2 {column name, transformation function, type}
3 A list composed of the above two, for example: {{column name, transformation function}, {column name, transformation function, type}, ...}

Note: The type specified here will not be forced to convert. You can only specify the type if you know the type clearly.

*/

The first parameter of the two functions is the table, the second parameter is the column name, and the third parameter is the exchange rate：

fx(sales, "price", 1.5)

 Document Links：Table.ReplaceValue 、Table.TransformColumns 

[ZhangKun · Accepted solution]

If you just need to rename one column name, you can add step and use Table.RenameColumns。

But, if you mean to convert the currency for all columns containing €，while replacing the column names with $, you can use the following function.

sample data sales:

function:

(Input_Table as table, CONVERSION_RATE as number) =>
let 
    // a list of all column names that contain the "€" symbol
    oldColumnNames = List.Select(Table.ColumnNames(Input_Table), each Text.Contains(_, "€")), 
    // replace "€" with "$"
    newColumnNames = List.Transform(oldColumnNames, each Text.Replace(_, "€", "$")), 
    calcTable = Table.ReplaceValue (
        Input_Table,
        null,
        null,
        (val, old, new) => val * CONVERSION_RATE,
        oldColumnNames
    ), 
    // rename column name
    renameColumns = Table.RenameColumns(calcTable, List.Zip({oldColumnNames, newColumnNames}))
in
    renameColumns

[ZhangKun · Accepted solution]

First of all, it is not recommended that you use the Table.ReplaceValue function because it is more complicated, but for the purpose of teaching, let's first use it to solve this problem:

(Input_Table as table, Input_Column_Name as text, CONVERSION_RATE as number) =>
Table.ReplaceValue (
    Input_Table,
    null,
    null,
    (val, old, new) => val * CONVERSION_RATE,
    {Input_Column_Name}
)

/*
Function definition:
Table.ReplaceValue(
    table as table, 
    oldValue as any, 
    newValue as any, 
    replacer as function, 
    columnsToSearch as list
) as table


The fourth parameter of this function is a function with three parameters. For the convenience of description, let's assume that this function is called fx. The three parameters of the fx function are:
1. Each value of the column specified in the fifth parameter of Table.ReplaceValue
2. The old value
3. The new value

For your question, we only need to multiply each value of the specified column by CONVERSION_RATE, so there is no need to specify the second and third parameters of Table.ReplaceValue (specified as null).
*/

In fact, a more understandable way is to use the Table.TransformColumns function:

(Input_Table as table, Input_Column_Name as text, CONVERSION_RATE as number) =>
Table.TransformColumns(
    Input_Table, 
    {Input_Column_Name, each _ * CONVERSION_RATE}
    //{Input_Column_Name, each _ * CONVERSION_RATE, Currency.Type}
)

/*
Function definition:
Table.TransformColumns(
    table as table, 
    transformOperations as list, 
    optional defaultTransformation as nullable function, 
    optional missingField as nullable number
) as table

This function takes the first two parameters, and the second parameter (transformOperations) is a list, which can be in the following three forms:
1 {column name, transformation function}
2 {column name, transformation function, type}
3 A list composed of the above two, for example: {{column name, transformation function}, {column name, transformation function, type}, ...}

Note: The type specified here will not be forced to convert. You can only specify the type if you know the type clearly.

*/

The first parameter of the two functions is the table, the second parameter is the column name, and the third parameter is the exchange rate：

fx(sales, "price", 1.5)

 Document Links：Table.ReplaceValue 、Table.TransformColumns 

[Mic1979]

Hello ZhangKun

I was trying to extend what you told me to more column, converting from Dollars to kDollars in this way:

let
CONVERSION_FROM_DOLLAR_TO_kDOLLAR = (
Input_Table as table,
Input_Column_Name_1 as text,
Input_Column_Name_2 as text,
Input_Column_Name_3 as text,
Input_Column_Name_4 as text) =>
Output_Column_Name_1 = Table.TransformColumns(Input_Table, {Input_Column_Name_1, each _ * 0.001},
Output_Column_Name_2 = Table.TransformColumns(Input_Table, {Input_Column_Name_2, each _ * 0.001},
Output_Column_Name_3 = Table.TransformColumns(Input_Table, {Input_Column_Name_3, each _ * 0.001},
Output_Column_Name_4 = Table.TransformColumns(Input_Table, {Input_Column_Name_4, each _ * 0.001})

in CONVERSION_FROM_DOLLAR_TO_kDOLLAR

However, this is not working.

Could you support on this as well?

As said, I am very basic in power query, for sure I am making stupid mistakes.

Thanks a lot.

[ZhangKun · Accepted solution]

What you wrote is rather cumbersome and inefficient, but to give you some inspiration, I will optimize your code step by step (and fix some errors at the same time).

1. fixed all error, but this version still cannot get the correct result(only step 4 is valid)

let
CONVERSION_FROM_DOLLAR_TO_kDOLLAR = (
    Input_Table as table,
    Input_Column_Name_1 as text,
    Input_Column_Name_2 as text,
    Input_Column_Name_3 as text,
    Input_Column_Name_4 as text
) =>
    // Because multiple values ​​are returned, the let statement should be used
    let
        // The first to third lines are missing the rightmost parenthesis
        Output_Column_Name_1 = Table.TransformColumns(Input_Table, {Input_Column_Name_1, each _ * 0.001}),
        Output_Column_Name_2 = Table.TransformColumns(Input_Table, {Input_Column_Name_2, each _ * 0.001}),
        Output_Column_Name_3 = Table.TransformColumns(Input_Table, {Input_Column_Name_3, each _ * 0.001}),
        Output_Column_Name_4 = Table.TransformColumns(Input_Table, {Input_Column_Name_4, each _ * 0.001})
    in 
        Output_Column_Name_4
in 
CONVERSION_FROM_DOLLAR_TO_kDOLLAR

 2. This version can get the correct result

let
CONVERSION_FROM_DOLLAR_TO_kDOLLAR = (
    Input_Table as table,
    Input_Column_Name_1 as text,
    Input_Column_Name_2 as text,
    Input_Column_Name_3 as text,
    Input_Column_Name_4 as text
) =>
    let
        Output_Column_Name_1 = Table.TransformColumns(Input_Table, {Input_Column_Name_1, each _ * 0.001}),
        Output_Column_Name_2 = Table.TransformColumns(Output_Column_Name_1, {Input_Column_Name_2, each _ * 0.001}),
        Output_Column_Name_3 = Table.TransformColumns(Output_Column_Name_2, {Input_Column_Name_3, each _ * 0.001}),
        Output_Column_Name_4 = Table.TransformColumns(Output_Column_Name_3, {Input_Column_Name_4, each _ * 0.001})
    in 
        Output_Column_Name_4
in 
CONVERSION_FROM_DOLLAR_TO_kDOLLAR

3. Simplify the code

(
    Input_Table as table,
    Input_Column_Name_1 as text,
    Input_Column_Name_2 as text,
    Input_Column_Name_3 as text,
    Input_Column_Name_4 as text
) =>
let
    Output_Column_Name_1 = Table.TransformColumns(Input_Table, {Input_Column_Name_1, each _ * 0.001}),
    Output_Column_Name_2 = Table.TransformColumns(Output_Column_Name_1, {Input_Column_Name_2, each _ * 0.001}),
    Output_Column_Name_3 = Table.TransformColumns(Output_Column_Name_2, {Input_Column_Name_3, each _ * 0.001}),
    Output_Column_Name_4 = Table.TransformColumns(Output_Column_Name_3, {Input_Column_Name_4, each _ * 0.001})
in 
    Output_Column_Name_4

4. Now we can get the code to run correctly, but it is not a good code, because as I said in my previous reply, the Table.TransformColumns function can write multiple column names, so we change it to the following code：

(
    Input_Table as table,
    // you can use a list of some column names or a column name(text type)
    // eg: {"col1", "col2"}
    // eg: "col1"
    Input_Column_Names as any, 
    // exchange rate or coefficient
    Input_Rate as number
) =>
Table.TransformColumns(
    Input_Table, 
    if Input_Column_Names is list then 
        List.Zip(
            {
                Input_Column_Names, 
                List.Repeat({each _ * Input_Rate}, List.Count(Input_Column_Names))
            }
        )
    else if Input_Column_Names is text then 
        {Input_Column_Names, each _ * Input_Rate}
    else 
        error "must be a list value or text value"
)

5. The above function is pretty good, you can use single or multiple column names. But as I said before: "Table.ReplaceValue is much simpler to implement".

(
    Input_Table as table,
    Input_Column_Names as any, 
    Input_Rate as number
) =>
Table.ReplaceValue(
    Input_Table, 
    null, 
    null, 
    (val, oldVal, newVal) => val * Input_Rate, 
    if Input_Column_Names is list then 
        Input_Column_Names
    else if Input_Column_Names is text then 
        {Input_Column_Names}
    else 
       error "must be a list value or text value" 
)

6. Final version。I think there may be a default value for the Input_Rate parameter, so I can modify it again, assuming the default value is 1.0

(
    Input_Table as table,
    Input_Column_Names as any, 
    // Optional parameters, you can use null, number(eg 1.0, 555) or ignore this parameter(same as use null)
    optional Input_Rate as number
) =>
Table.ReplaceValue(
    Input_Table, 
    null, 
    null, 
    // if you want to change the default value, you can modify 1.0 to the value you want
    (val, oldVal, newVal) => val * (Input_Rate ?? 1.0), 
    if Input_Column_Names is list then 
        Input_Column_Names
    else if Input_Column_Names is text then 
        {Input_Column_Names}
    else 
       error "must be a list value or text value" 
)

[Mic1979]

Many thanks for your help.

[Mic1979]

And is it possible, after the conversion, to add another step, always inside this function, to change a character in the column name from € to $?

It would be great.

many thanks.

[ZhangKun · Accepted solution]

If you just need to rename one column name, you can add step and use Table.RenameColumns。

But, if you mean to convert the currency for all columns containing €，while replacing the column names with $, you can use the following function.

sample data sales:

function:

(Input_Table as table, CONVERSION_RATE as number) =>
let 
    // a list of all column names that contain the "€" symbol
    oldColumnNames = List.Select(Table.ColumnNames(Input_Table), each Text.Contains(_, "€")), 
    // replace "€" with "$"
    newColumnNames = List.Transform(oldColumnNames, each Text.Replace(_, "€", "$")), 
    calcTable = Table.ReplaceValue (
        Input_Table,
        null,
        null,
        (val, old, new) => val * CONVERSION_RATE,
        oldColumnNames
    ), 
    // rename column name
    renameColumns = Table.RenameColumns(calcTable, List.Zip({oldColumnNames, newColumnNames}))
in
    renameColumns

[Mic1979]

Many thanks.

It also helps for the future beacuse it is making me understand how to have multiple steps in the same function. You are great!!

[Mic1979]

it is perfect. Thanks

[dufoq3]

Hi @Mic1979, what about this? Just replace 1.3 with your parameter.

Before

After

let
    Source = Table.FromList({10, 20, 30, 40, 50}, (x)=> {x}, {"Amount"}),
    Custom1 = Table.TransformColumns(Source, {{"Amount", each _ * 1.3, type number}})
in
    Custom1

[Mic1979]

Thanks.

However, I am very basic to power query, and I don't know how to match your suggestion with my function.

Could you be clearer please?

Thanks again for your support.

[dufoq3]

You don't need any function - just use my query. If yo don't know how - read note below my post.