cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Combine 2 tables problem

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

1 ACCEPTED SOLUTION
Super User

@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"``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Helper IV

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

Super User

@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"``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Community Champion

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"``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!