Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |