Power BI Mquery

[rviswa]

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

[mahoneypat · Accepted solution]

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

[rviswa]

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

Thanks