cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rviswa
New Member

Power BI Mquery

Hi 

 

I have the below table :-

Project Code   Start Date  End Date

A                      05/06/2020   20/07/2020

B                      13/07/2020    14/09/2020

 

I need to transform the data and get the output in the below format:-

Project Code  Open Month  Count

A                     01/06/2020   1

A                     01/07/2020   1

B                     01/07/2020   1

B                    01/08/2020    1

B                    01/09/2020    1

 

How to do this in Power BI M query? 

 

Thanks

R

 

 

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  The key step is the custom column one.  Note that I am in a different locale, so my dates are in MM/DD/YYYY format, but the code should work fine as is.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLTN9U3MjAyADINDWDsWJ1oJSegiDGQC5O11DeGSsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Code" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Code", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateList", each let start = Date.StartOfMonth([Start Date]),
months = Number.Round(Duration.Days(Date.StartOfMonth([End Date]) - start)/30,0)
in 
List.Transform({0..months-1}, each Date.AddMonths(start, _))),
    #"Expanded DateList" = Table.ExpandListColumn(#"Added Custom", "DateList"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded DateList",{"Start Date", "End Date"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Count", each 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Count", Int64.Type}, {"DateList", type date}})
//List.Transform({0..months}, Date.AddMonths(start, _)))

in
    #"Changed Type1"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User
Super User

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  The key step is the custom column one.  Note that I am in a different locale, so my dates are in MM/DD/YYYY format, but the code should work fine as is.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLTN9U3MjAyADINDWDsWJ1oJSegiDGQC5O11DeGSsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Code" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Code", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateList", each let start = Date.StartOfMonth([Start Date]),
months = Number.Round(Duration.Days(Date.StartOfMonth([End Date]) - start)/30,0)
in 
List.Transform({0..months-1}, each Date.AddMonths(start, _))),
    #"Expanded DateList" = Table.ExpandListColumn(#"Added Custom", "DateList"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded DateList",{"Start Date", "End Date"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Count", each 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Count", Int64.Type}, {"DateList", type date}})
//List.Transform({0..months}, Date.AddMonths(start, _)))

in
    #"Changed Type1"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


The code works great, I had to customize it little bit though.

Thanks

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!