cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JonV Regular Visitor
Regular Visitor

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:

 

currencyconversion.PNG

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Setting conversion rate for current month = last

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.

2.PNGCapture.PNG

 

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Setting conversion rate for current month = last

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.

2.PNGCapture.PNG

 

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JonV Regular Visitor
Regular Visitor

Re: Setting conversion rate for current month = last

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 310 members 3,297 guests
Please welcome our newest community members: