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.
Hi,
I have an exchange rate table and I would like to convert it from this
Currency.CurrencyCode | Currency (2).CurrencyCode | From YYYY/MM | ExchangeRateValidDateFrom | ExchangeRateValidDateTill | ExchangeRate | ExchangeRateType.ExchangeRateTypeCode |
EUR | CNY | 2012/01 | 1/01/2012 00:00 | 28/01/2012 00:00 | 8,1588 | ACCOUNTING |
EUR | CNY | 2012/02 | 29/01/2012 00:00 | 3/03/2012 00:00 | 8,3121 | ACCOUNTING |
EUR | CNY | 2012/03 | 4/03/2012 00:00 | 14/03/2012 00:00 | 8,4608 | ACCOUNTING |
EUR | CNY | 2012/04 | 14/03/2012 00:00 | 30/04/2012 00:00 | 8,4089 | ACCOUNTING |
to this
Date | Exchange Rate |
1/01/2012 | 8,1588 |
2/01/2012 | 8,1588 |
3/01/2012 | 8,1588 |
4/01/2012 | 8,1588 |
5/01/2012 | 8,1588 |
6/01/2012 | 8,1588 |
7/01/2012 | 8,1588 |
8/01/2012 | 8,1588 |
9/01/2012 | 8,1588 |
10/01/2012 | 8,1588 |
11/01/2012 | 8,1588 |
12/01/2012 | 8,1588 |
13/01/2012 | 8,1588 |
14/01/2012 | 8,1588 |
15/01/2012 | 8,1588 |
16/01/2012 | 8,1588 |
17/01/2012 | 8,1588 |
18/01/2012 | 8,1588 |
19/01/2012 | 8,1588 |
20/01/2012 | 8,1588 |
21/01/2012 | 8,1588 |
22/01/2012 | 8,1588 |
23/01/2012 | 8,1588 |
24/01/2012 | 8,1588 |
25/01/2012 | 8,1588 |
26/01/2012 | 8,1588 |
27/01/2012 | 8,1588 |
28/01/2012 | 8,1588 |
29/01/2012 | 8,3121 |
30/01/2012 | 8,3121 |
31/01/2012 | 8,3121 |
1/02/2012 | 8,3121 |
2/02/2012 | 8,3121 |
3/02/2012 | 8,3121 |
4/02/2012 | 8,3121 |
5/02/2012 | 8,3121 |
6/02/2012 | 8,3121 |
7/02/2012 | 8,3121 |
8/02/2012 | 8,3121 |
9/02/2012 | 8,3121 |
10/02/2012 | 8,3121 |
11/02/2012 | 8,3121 |
12/02/2012 | 8,3121 |
13/02/2012 | 8,3121 |
14/02/2012 | 8,3121 |
15/02/2012 | 8,3121 |
16/02/2012 | 8,3121 |
17/02/2012 | 8,3121 |
18/02/2012 | 8,3121 |
19/02/2012 | 8,3121 |
20/02/2012 | 8,3121 |
21/02/2012 | 8,3121 |
22/02/2012 | 8,3121 |
23/02/2012 | 8,3121 |
24/02/2012 | 8,3121 |
25/02/2012 | 8,3121 |
26/02/2012 | 8,3121 |
27/02/2012 | 8,3121 |
28/02/2012 | 8,3121 |
29/02/2012 | 8,3121 |
1/03/2012 | 8,3121 |
2/03/2012 | 8,3121 |
3/03/2012 | 8,3121 |
4/03/2012 | 8,4608 |
5/03/2012 | 8,4608 |
6/03/2012 | 8,4608 |
7/03/2012 | 8,4608 |
8/03/2012 | 8,4608 |
9/03/2012 | 8,4608 |
10/03/2012 | 8,4608 |
11/03/2012 | 8,4608 |
12/03/2012 | 8,4608 |
13/03/2012 | 8,4608 |
14/03/2012 | 8,4608 |
15/03/2012 | 8,4089 |
16/03/2012 | 8,4089 |
17/03/2012 | 8,4089 |
18/03/2012 | 8,4089 |
19/03/2012 | 8,4089 |
20/03/2012 | 8,4089 |
21/03/2012 | 8,4089 |
22/03/2012 | 8,4089 |
23/03/2012 | 8,4089 |
24/03/2012 | 8,4089 |
25/03/2012 | 8,4089 |
26/03/2012 | 8,4089 |
Is this possible in the Query Editor? To repeat the exchange rate for every day until the date is equal to the "ExchangeRateValidDateTill" date.
Solved! Go to Solution.
Hi @svandamme,
It is possible. Please find the steps I processed in Power query ( you can find elegant ones).
Please change the Source or add the steps after the Source.
To sum up:
- I created Datediff :the difference between the date from and the date still ( transform the data to Whole number)
- Create a repeated list based on the Datediff
- Expand the table
-Remove unnecesseray columns
-Regroup and created index
-Add date with the index created
-Remove unnecessary columns
Hope it helps
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ExchangeRateValidDateFrom", type datetime}, {"ExchangeRateValidDateTill", type datetime}, {"ExchangeRate", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Datediff", each [ExchangeRateValidDateTill]-[ExchangeRateValidDateFrom]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Datediff", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each List.Repeat({[Datediff]},[Datediff])), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Datediff", "Custom"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"ExchangeRateValidDateTill"}, {{"Count", each Table.AddIndexColumn(_, "Index1", 0, 1), type table}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"ExchangeRateValidDateFrom", "ExchangeRate", "Index1"}, {"Count.ExchangeRateValidDateFrom", "Count.ExchangeRate", "Count.Index1"}), #"Added Custom2" = Table.AddColumn(#"Expanded Count", "Date", each Date.AddDays([Count.ExchangeRateValidDateFrom],[Count.Index1])), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"ExchangeRateValidDateTill", "Count.ExchangeRateValidDateFrom", "Date", "Count.ExchangeRate", "Count.Index1"}), #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Count.Index1"}) in #"Removed Columns1"
Ninter
Hi @svandamme,
It is possible. Please find the steps I processed in Power query ( you can find elegant ones).
Please change the Source or add the steps after the Source.
To sum up:
- I created Datediff :the difference between the date from and the date still ( transform the data to Whole number)
- Create a repeated list based on the Datediff
- Expand the table
-Remove unnecesseray columns
-Regroup and created index
-Add date with the index created
-Remove unnecessary columns
Hope it helps
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ExchangeRateValidDateFrom", type datetime}, {"ExchangeRateValidDateTill", type datetime}, {"ExchangeRate", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Datediff", each [ExchangeRateValidDateTill]-[ExchangeRateValidDateFrom]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Datediff", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each List.Repeat({[Datediff]},[Datediff])), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Datediff", "Custom"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"ExchangeRateValidDateTill"}, {{"Count", each Table.AddIndexColumn(_, "Index1", 0, 1), type table}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"ExchangeRateValidDateFrom", "ExchangeRate", "Index1"}, {"Count.ExchangeRateValidDateFrom", "Count.ExchangeRate", "Count.Index1"}), #"Added Custom2" = Table.AddColumn(#"Expanded Count", "Date", each Date.AddDays([Count.ExchangeRateValidDateFrom],[Count.Index1])), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"ExchangeRateValidDateTill", "Count.ExchangeRateValidDateFrom", "Date", "Count.ExchangeRate", "Count.Index1"}), #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Count.Index1"}) in #"Removed Columns1"
Ninter
That's it! You are amazing! Thank you so much!
I just made one small change
I changed the formula for the Datediff custom column to: Number.From([ExchangeRateValidDateTill])-Number.From([ExchangeRateValidDateFrom])+1
The reason why I added the +1 is because the date the exchange rate expired was not included in the list (see screenshot)
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |