Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 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 month that falls within the start and end-dates from the first table.

 

For example:

 

Table1

ID   Start                  End                   Value
1     05/Aug/2017    15/Nov/2017     110
2     10/Dec/2018    02/Feb/2019       80
3     12/May/2016   23/Jun/2018       240
etc


The new table should look like this:

 

Table2
ID      Month      Value
1        Aug-2017    110
1        Sep-2017    110
1        Oct-2017    110
1        Nov-2017   110
2        Dec-2018    80
2        Jan-2019     80
2       Feb-2019     80
3       May-2016   240
3       Jun-2016    240
etc

 

Any suggestions on how to accomplish this?  Thanks.

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 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] )

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 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
        Community Champion
        7 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
      7 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
        2 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?