Special Transpose in Query Editor

[svandamme]

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.

[Interkoubess · Accepted 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

[svandamme]

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)