cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rjimenez
Helper I
Helper I

Cretae rows from every month in date-range

Hi!

 

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

Captura1.JPG

 

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

Captura2.JPG

 

Regards!

 

1 ACCEPTED SOLUTION
rjimenez
Helper I
Helper I

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])

View solution in original post

3 REPLIES 3
rjimenez
Helper I
Helper I

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])

View solution in original post

HotChilli
Super User II
Super User II

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
Super User III
Super User III

Hi @rjimenez ,

 

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


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors