货币( SCD表)和请求表

Question

我有两张桌子，我需要在最需要的时候用当地货币兑换成美元。

Table 1 
Product request  / Request Date / Local Currency 
1                   01/01/2022    GBP

Table 2
Currency source / Currency Target / Effective From / Effective To / Rate 
GBP                  USD             01/12/2021       31/12/2021    1.1
GBP                  USD             01/01/2022       31/12/3039    2.2

表1所需的列是美元转换，其值为2.2。

那么，在Power中，是否有一种方法可以完成该列的查找，并为SCD货币表生成该列？

Answer 1

在Power Query中，您可以

在表2中读取

• 并对其进行缓冲(因此不必对表1中的每一行进行读取)
• 使用Table.SelectRows方法返回所需的输出

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    Source2 = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]),
    currencyTable = Table.TransformColumnTypes(Source2,{
        {"Currency source", type text},{"Currency Target", type text},
        {"Effective From", type date},{"Effective To", type date},
        {"Rate", type number}
    }),

    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Product request", Int64.Type}, {"Request Date", type date}, {"Local Currency", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "USD Rate", each 
        Table.SelectRows(currencyTable, 
            (t)=>t[Currency source] = [Local Currency] and 
                 t[Effective From] <=[Request Date] and 
                 t[Effective To] >= [Request Date])[Rate]{0})
in
    #"Added Custom"

​

​