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 mon...
  • 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] )
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity 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"