Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
gomezc73
Helper V
Helper V

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"


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
gomezc73
Helper V
Helper V

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"


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors