How to create a dynamic table with repeating dimensions for each date?

[jopezzo]

Hi!

I am trying to build a table where some dimensions ("From currency" and "To currency") are automatically repeated for each date in a certain time period.

For example:

Imagine that my dimensions are the following ones:

From  To

EUR    USD

EUR    SEK

EUR    CNY

Then I have all dates from 01/01/2013 to 31/12/2019 in a table.

To create these dates, I used the following formula: DateTable = CALENDAR(DATE(2013,01,01),DATE(2019,12,31))

That is working well.

Now what I am not able to do is to have my "From" and "To" dimensions repeating for each date.

See below what I would like to do:

Table 1	Date
	1/01/2013
	2/01/2013
	3/01/2013
Table 2	From	To
	EUR	USD
	EUR	SEK
	EUR	CNY
Dynamic table	Date	From	To
	1/01/2013	EUR	USD
	1/01/2013	EUR	SEK
	1/01/2013	EUR	CNY
	2/01/2013	EUR	USD
	2/01/2013	EUR	SEK
	2/01/2013	EUR	CNY
	3/01/2013	EUR	USD
	3/01/2013	EUR	SEK
	3/01/2013	EUR	CNY

Table 1 would be built with formula DateTable = CALENDAR(DATE(2013,01,01),DATE(2019,12,31))

Table 2 is a manual input in a table

Dynamic table would repeat all currencies From and To for each date.

Thanks for your help!

[v-yuezhe-msft · Accepted solution]

@jopezzo,

Check if the following code returns your expected result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0NUtJRCg12UYrVgfGCXb2ReM5+kWCes4cbkkoQjVAJUgOViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"To", type text}, {"From", type text}}),
 #"Added Custom" = Table.AddColumn(#"Changed Type", "StartDate", each #date(2013,1,1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End date", each #date(2019,12,31)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Dates([StartDate],Duration.Days(Duration.From([End date]-[StartDate]))+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Date"}})
in
    #"Renamed Columns"

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@jopezzo,

Check if the following code returns your expected result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0NUtJRCg12UYrVgfGCXb2ReM5+kWCes4cbkkoQjVAJUgOViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"To", type text}, {"From", type text}}),
 #"Added Custom" = Table.AddColumn(#"Changed Type", "StartDate", each #date(2013,1,1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End date", each #date(2019,12,31)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Dates([StartDate],Duration.Days(Duration.From([End date]-[StartDate]))+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Date"}})
in
    #"Renamed Columns"

Regards,
Lydia

[jopezzo]

Hi Lydia,

It is working with my theoretical example. Now, with actual data (my currencies in my Table1), how do I do? It looks like the currencies I gave you as example are somehow stored in the beginning of the code (the "json" part, which I don't know at all).

Thanks again for your support!

[v-yuezhe-msft · Accepted solution]

@jopezzo,

I directly use Enter data option in Power BI Desktop to create the currency table, in your scenario, you can copy the following code part and paste it after the source code of the current query in Advanced Editor.

 #"Added Custom" = Table.AddColumn(#"Changed Type", "StartDate", each #date(2013,1,1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End date", each #date(2019,12,31)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Dates([StartDate],Duration.Days(Duration.From([End date]-[StartDate]))+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Date"}})
in
    #"Renamed Columns"

Regards,
Lydia

[v-yuezhe-msft]

@jopezzo,

1. Add a blank query in Query Editor of Power BI Desktop, and then paste the following code to the advanced editor of the query. Then rename the query to DateTable.

let
    Source = #date(2013,1,1),
    Custom1 = List.Dates(Source, Number.From(#date(2019,12,31))- Number.From(Source) ,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each "EUR")
in
    #"Added Custom"

2. Add another blank query in Query Editor of Power BI Desktop, and then paste the following code to the advanced editor of the query.

let
    Source = Table.NestedJoin(DateTable,{"Custom"},#"Table 2",{"From"},"Table 2",JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(Source, "Table 2", {"From", "To"}, {"Table 2.From", "Table 2.To"})
in
    #"Expanded Table 2"

Regards,
Lydia

[jopezzo]

@v-yuezhe-msft: thanks for your help. It is very close from the solution I need. I see that in your query you did, you created a custom column for EUR, since in my example column "From" was only EUR. I should have showed different examples, since I would like this column from to be dynamic as well (I do not have only EUR in fact). How can I do?

[v-yuezhe-msft]

@jopezzo,

Could you please give a more detailed example? If your From column contains different values, you can duplicate this query and filter data to contain only one value in the duplicated queries, then apply my code.

After all, you can append all your queries into one query.

Regards,
Lydia

[jopezzo]

Hi Lydia, It is more or less the same, except that I would have different values in column "From":

Table 1	Date
	1/01/2013
	2/01/2013
	3/01/2013
Table 2	From	To
	EUR	USD
	EUR	SEK
	EUR	CNY
	CHF	USD
	USD	SEK
	CNY	USD

In your code, column "From" seems to be written as "EUR" for each date. What I would like to have is to have all my values in column "From" repeated for each date. Same for column "To".

Thanks, Lydia!