Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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.
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.
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