I want craete table chart and show all column from table A but curreency rate get from table B in cu

[Anan2609]

 TableA

invoice no	invoicedate	invoicevalue	currency
inv1	02-01-2022	100	usd

Table B

currency	inrrate	effectivefrom
usd	70	25-12-2021
usd	75	01-01-2022
usd	78	05-01-2022
ueuro	98	02-01-2022

I want craete table chart and show all column from table A but curreency rate get from table B in currency column

 OUTPUT

invoice no	invoicedate	invoicevalue	currency
inv1	02-01-2022	100	75

[v-yingjl · Accepted solution]

Hi @Anan2609 ,

You can try this query in TableA:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyswrM1TSUTIyMDLSN9Q3AjINDQyAZGlxilJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"invoice no" = _t, #"invoice date" = _t, #"invoice value" = _t, currency = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"invoice no", type text}, {"invoice date", type date}, {"invoice value", Int64.Type}, {"currency", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"currency"}, TableB, {"currency"}, "TableB", JoinKind.LeftOuter),
    #"Added Custom" = 
        Table.AddColumn(
            #"Merged Queries", "Result", each 
                let x = [invoice date] 
                in 
                    Table.Max(Table.SelectRows([TableB], each [effective from] <= x),{"inrrate"})[inrrate]
                , Int64.Type
        ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"currency", "TableB"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You do this with a proper data model including a calendar table. Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post (as you already did) or use one of the file services it will be easier to work with. 

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523