Forum Discussion

JonV's avatar
JonV
Helper II
7 years ago
Solved

Setting conversion rate for current month = last

I have a table that's in CAD. I want to convert it to USD using a conversion factor that comes from a table that I merge in.

 

Trouble is that the table of conversion factors only has end of month averages. Unfortuantely, that means no conversion factor for the current month, which breaks things.

 

The table with the currency conversion in it looks like this:

 

 

Is it possible to add a row to this in power query? Or to write something to replace the nulls with the last value from the lookup table?

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi JonV ,

     

    New a blank query and enter the code as below to get a table of Month- year of today.

    let
    Source =List.DateTimes(DateTime.LocalNow(),1,#duration(0, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Date.ToText([Column1],"MMM-yy")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText([Column1],"MMMM-yyyy")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Month-Year"}})
in
    #"Renamed Columns"

    Then we can append a new table like this and fill the column1 down.

     

    M code for your reference.

    let
    Source = Table.Combine({Table, Query1}),
    #"Filled Down" = Table.FillDown(Source,{"Column1"})
in
    #"Filled Down"

    Also you can check the pbix as attached.

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi JonV ,

     

    New a blank query and enter the code as below to get a table of Month- year of today.

    let
    Source =List.DateTimes(DateTime.LocalNow(),1,#duration(0, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Date.ToText([Column1],"MMM-yy")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText([Column1],"MMMM-yyyy")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Month-Year"}})
in
    #"Renamed Columns"

    Then we can append a new table like this and fill the column1 down.

     

    M code for your reference.

    let
    Source = Table.Combine({Table, Query1}),
    #"Filled Down" = Table.FillDown(Source,{"Column1"})
in
    #"Filled Down"

    Also you can check the pbix as attached.

     

    • JonV's avatar
      JonV
      Helper II
      7 years ago

      Thanks for the help. However, in implementing it, I found that I could append the current month table directly to the original table to avoid creating of the 3rd table before filling down.