Cretae rows from every month in date-range

[Anonymous]

Hi!

is it powible to transform a table whith  with data-range:

To this (a date range for every month into initial data-range):

Regards!

[Anonymous · Accepted solution]

I get the solution:

First i create a column witht init date:

List.Distinct( List.Transform(
List.Dates([INIT],Number.From([END]-[INIT])+1,#duration(1,0,0,0)),each Date.StartOfMonth(_)))

Second I create the list as rows.

Last I create enddatecolumn:

Date.EndOfMonth([INITMONTH])

[HotChilli]

Power Query code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjEMcYyDGGcGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, init = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"init", type date}, {"end", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each {Number.From([init])..Number.From([end])}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type date}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Changed Type1", "Start of Month", each Date.StartOfMonth([Custom.1]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Custom.1]), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted End of Month",{ "init", "end", "Custom.1"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

There are quite a few steps but 90% of it is done using the interface.

[Pragati11]

Hi @Anonymous ,

I don't think you can do this in Power BI. You will have to bring in a complete file with the filled values in the data.

Thanks,

Pragati