Forum Discussion

gomezc73's avatar
gomezc73
Helper V
5 years ago
Solved

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/20...
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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's avatar
camargos88
Community Champion
5 years ago

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"