Hello guys,
I have a problem merging two tables, Could you please help me?
Table#1: is a calendar table, only have a column with the date, from 01/01/2014
Date |
1/1/2014 |
1/2/2014 |
1/3/2014 |
1/4/2014 |
1/5/2014 |
1/6/2014 |
1/7/2014 |
1/8/2014 |
1/9/2014 |
1/10/2014 |
1/11/2014 |
1/12/2014 |
1/13/2014 |
1/14/2014 |
1/15/2014 |
1/16/2014 |
1/17/2014 |
1/18/2014 |
1/19/2014 |
1/20/2014 |
1/21/2014 |
1/22/2014 |
1/23/2014 |
1/24/2014 |
1/25/2014 |
1/26/2014 |
1/27/2014 |
Table#2: Have the exchange rate downloaded weekly on Saturdays.
Curr From | Cur To | Effective Date | Exchange Rate |
EUR | USD | 01/11/14 | 1.3672000 |
EUR | USD | 01/18/14 | 1.3557000 |
EUR | USD | 01/25/14 | 1.3685000 |
EUR | USD | 01/31/14 | 1.3484000 |
EUR | USD | 02/08/14 | 1.3618000 |
EUR | USD | 02/15/14 | 1.3686000 |
EUR | USD | 02/22/14 | 1.3745000 |
EUR | USD | 02/28/14 | 1.3811000 |
EUR | USD | 03/08/14 | 1.3862000 |
EUR | USD | 03/15/14 | 1.3916000 |
EUR | USD | 03/22/14 | 1.3807000 |
I need combine these two tables to obtain. It is possible?. I tried to merge them but only get data where the date exist in both tables. I need all dates from table 1..
Date | Curr From | Curr To | Effective Date | Exchange Rate |
1/1/2014 | EUR | USD | ||
1/2/2014 | EUR | USD | ||
1/3/2014 | EUR | USD | ||
1/4/2014 | EUR | USD | ||
1/5/2014 | EUR | USD | ||
1/6/2014 | EUR | USD | ||
1/7/2014 | EUR | USD | ||
1/8/2014 | EUR | USD | ||
1/9/2014 | EUR | USD | ||
1/10/2014 | EUR | USD | ||
1/11/2014 | EUR | USD | 1/11/2014 | 1.367200 |
1/12/2014 | EUR | USD | ||
1/13/2014 | EUR | USD | ||
1/14/2014 | EUR | USD | ||
1/15/2014 | EUR | USD | ||
1/16/2014 | EUR | USD | ||
1/17/2014 | EUR | USD | ||
1/18/2014 | EUR | USD | 1/18/2014 | 1.355700 |
1/19/2014 | EUR | USD | ||
1/20/2014 | EUR | USD | ||
1/21/2014 | EUR | USD |
Solved! Go to Solution.
@gomezc73 - How about this? PBIX is attached below sig
let
Source = Table.NestedJoin(Table, {"Date"}, #"Table (2)", {"Effective Date"}, "Table (2)", JoinKind.LeftOuter),
#"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Curr From", "Cur To", "Effective Date", " Exchange Rate "}, {"Table (2).Curr From", "Table (2).Cur To", "Table (2).Effective Date", "Table (2). Exchange Rate "}),
#"Filled Down" = Table.FillDown(#"Expanded Table (2)",{"Table (2).Curr From", "Table (2).Cur To", "Table (2). Exchange Rate "})
in
#"Filled Down"
Thank you!!. that is all i need..
@gomezc73 - How about this? PBIX is attached below sig
let
Source = Table.NestedJoin(Table, {"Date"}, #"Table (2)", {"Effective Date"}, "Table (2)", JoinKind.LeftOuter),
#"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Curr From", "Cur To", "Effective Date", " Exchange Rate "}, {"Table (2).Curr From", "Table (2).Cur To", "Table (2).Effective Date", "Table (2). Exchange Rate "}),
#"Filled Down" = Table.FillDown(#"Expanded Table (2)",{"Table (2).Curr From", "Table (2).Cur To", "Table (2). Exchange Rate "})
in
#"Filled Down"
Hi @gomezc73 ,
You can use this code on advanced editor to create a week values for each line:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBJDoAgDAXQqxjWRjow1L1eQMOKcP9riHElLYSQLl74P63VneVyqyv30V9Aj/2GPi7vwY1TJgBwbdVS/jLGPJEUhz8lTiQP6UGCKcnDkJ5QJhJVeppIor/Mwe7Z5ZAuiKZk1VOSvU9WPXe0e7LqKfBtvj0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Curr From" = _t, #"Cur To" = _t, #"Effective Date" = _t, #" Exchange Rate " = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,".",",",Replacer.ReplaceText,{" Exchange Rate "}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Effective Date", type date}}, "en-US"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{" Exchange Rate ", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each List.Dates(Date.AddDays([Effective Date], -7),
7, #duration(1,0,0,0))),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1")
in
#"Expanded Custom.1"
After it, you can create a date table and relate them.
let
Source =
let _minDate = Date.StartOfMonth(List.Min(Table[Date])) in
List.Dates(
_minDate,
Duration.TotalDays(List.Max(Table[Date]) - _minDate),
#duration(1,0,0,0)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
#"Changed Type"