Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

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

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.