Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Create table with repeating values based on start and end dates

I have a table with a number of records that have start a start-date and an end-date.  From this I'd like to create a second table that has a separate record that repeats a certain value for each mon...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    One way is to write a calculated table

     

    From the Modelling Tab>>New Table

     

    Calculated Table =
VAR temp =
    GENERATE (
        Table1,
        VAR no_of_month =
            DATEDIFF ( [Start], [End], MONTH ) + 1
        RETURN
            SELECTCOLUMNS ( GENERATESERIES ( 1, no_of_month ), "MyValues", [Value] )
    )
VAR temp2 =
    ADDCOLUMNS ( temp, "Month", EOMONTH ( [Start], [MyValues] - 1 ) )
RETURN
    SELECTCOLUMNS ( temp2, "ID", [ID], "Month", [Month], "Value", [Value] )
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Anonymous

 

One way is to write a calculated table

 

From the Modelling Tab>>New Table

 

Calculated Table =
VAR temp =
    GENERATE (
        Table1,
        VAR no_of_month =
            DATEDIFF ( [Start], [End], MONTH ) + 1
        RETURN
            SELECTCOLUMNS ( GENERATESERIES ( 1, no_of_month ), "MyValues", [Value] )
    )
VAR temp2 =
    ADDCOLUMNS ( temp, "Month", EOMONTH ( [Start], [MyValues] - 1 ) )
RETURN
    SELECTCOLUMNS ( temp2, "ID", [ID], "Month", [Month], "Value", [Value] )
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Anonymous

       

      Also you can use "M"/Power Query/Query Editor to do this transformation.

      Please see attached file's Query Editor

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOLCktSkms1FFwLE0vLS5RMNVRMDIwNAdKhaem5KUWg+X88stSc5NSixQMEdKGhgZKsTrRSkZAtm9+HlidS2oyVJ0BWJ0Fig1uqUlFpYlFlQpGYElLoKQFxAxjIDMko7QIYptvYqWCIUSNGYoBXqV5qQpGxnCjjUyA2mMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t, End = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start", type date}, {"End", type date}, {"Value", Int64.Type}}),
    Months=Table.AddColumn(ChangedType, "Months", each let mystart=[Start], myend=[End] in
List.Generate(()=>Date.EndOfMonth(mystart),each _ <= Date.EndOfMonth(myend),each Date.AddMonths(_,1))),
    #"Removed Columns" = Table.RemoveColumns(Months,{"Start", "End"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Months", "Value"}),
    #"Expanded Months" = Table.ExpandListColumn(#"Reordered Columns", "Months")
in
    #"Expanded Months"
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thanks - that works like a charm!  Appreciate the quick reponse as well. Cheers.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

    Is it possible to do something similar but basend on days not months?

     

    • anshulgrover7's avatar
      anshulgrover7
      Regular Visitor
      3 years ago

      Hi Marcin211, Did you find any solution to this? I am trying to achieve the same but no luck.

       

       

      What I need is 

       

      Date             Hours in a Day   Machine No.

      1-1-2023           24                     XX

      1-2-2023           24                     XX
      1-3-2023           24                     XX
      1-1-2023           24                     XY
      1-2-2023           24                     XY

      and so on. 
      The machine numbers are finite,. Any suggestions how can I achieve this?