cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gomezc73
Helper III
Helper III

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 FromCur ToEffective Date Exchange Rate 
EURUSD01/11/14     1.3672000
EURUSD01/18/14     1.3557000
EURUSD01/25/14     1.3685000
EURUSD01/31/14     1.3484000
EURUSD02/08/14     1.3618000
EURUSD02/15/14     1.3686000
EURUSD02/22/14     1.3745000
EURUSD02/28/14     1.3811000
EURUSD03/08/14     1.3862000
EURUSD03/15/14     1.3916000
EURUSD03/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..

 
DateCurr FromCurr ToEffective Date Exchange Rate 
1/1/2014EURUSD  
1/2/2014EURUSD  
1/3/2014EURUSD  
1/4/2014EURUSD  
1/5/2014EURUSD  
1/6/2014EURUSD  
1/7/2014EURUSD  
1/8/2014EURUSD  
1/9/2014EURUSD  
1/10/2014EURUSD  
1/11/2014EURUSD1/11/2014             1.367200
1/12/2014EURUSD  
1/13/2014EURUSD  
1/14/2014EURUSD  
1/15/2014EURUSD  
1/16/2014EURUSD  
1/17/2014EURUSD  
1/18/2014EURUSD1/18/2014             1.355700
1/19/2014EURUSD  
1/20/2014EURUSD  
1/21/2014EURUSD  
 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
gomezc73
Helper III
Helper III

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

Greg_Deckler
Super User
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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
camargos88
Community Champion
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!



Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors