Forum Discussion

rviswa's avatar
rviswa
Regular Visitor
6 years ago
Solved

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

 

 

  • 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

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee

    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's avatar
      rviswa
      Regular Visitor

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

      Thanks