Combine 2 tables problem

[gomezc73]

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

[gomezc73]

Thank you!!. that is all i need.. 

[Greg_Deckler · Accepted 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"

[camargos88]

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"