Forum Discussion

jopezzo's avatar
jopezzo
Icon for Helper I rankHelper 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

jopezzo's avatar
jopezzo
Icon for Helper I rankHelper 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'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
    Icon for Helper I rankHelper 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!