Forum Discussion
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
- mahoneypatMicrosoft 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
- rviswaRegular Visitor
The code works great, I had to customize it little bit though.
Thanks