Forum Discussion

jopezzo's avatar
jopezzo
Helper I
8 years ago
Solved

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 dim...
Need Help
  • Anonymous's avatar
    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's avatar
    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

Anonymous's avatar
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

jopezzo's avatar
jopezzo
Helper I
8 years ago

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

 

Table 1Date 
 1/01/2013 
 2/01/2013 
 3/01/2013 
   
Table 2FromTo
 EURUSD
 EURSEK
 EURCNY
 CHFUSD
 USDSEK
 CNYUSD

 

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!