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

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!

Need Help

Anonymous

8 years ago

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

Anonymous

8 years ago

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

7 Replies

Anonymous
Not applicable
8 years ago
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
  Helper I
  8 years ago
  Anonymous: 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?
  - Anonymous
    Not applicable
    8 years ago
    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

Anonymous

Not applicable

8 years ago

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
Helper I
8 years ago
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!
- Anonymous
  Not applicable
  8 years ago
  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

Forum Discussion

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

7 Replies

Related content

Paginated Report headers not repeating

degenerate dimensions

Dynamic Calculated Column as Visual dimension

RLS on multiple dimensions

Dynamic change Ranking based on Dimension change