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

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