Extract dates(start of the month) with M-Query

[Anonymous]

Hello guys, 

I'm new to M-Query. I have been trying to figure out to extract dates from a date using a custom column on PowerBI.

Below is a picture with my problem. 

Thanks alot 

[Stachu]

add this as a custom column, then expand to rows

{[start_date],Date.StartOfMonth([end_date])}

it will produce the second layout

[Anonymous]

Thanks, 

but there was a mistake in the second layout. I would like the total to be divided into equal parts, and also that this custom column displays the start of the month. 

[Stachu]

does it mean just dividing by 2, or if the range would span across say 3 months then it should be expanded into 3 rows, and divided by 3?

[Anonymous]

Yes exactly, that if the range spans across 3 months it should be expanded into 3 rows and divided by 3. 

[Stachu]

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck/NV9JRMjIwtNAzMAQiOMdYzxjEMVOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [classes = _t, start_date = _t, end_date = _t, total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"classes", type text}, {"start_date", type date}, {"end_date", type date}, {"total", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "dates", each List.Distinct(List.Transform(List.Dates([start_date], Duration.Days([end_date]-[start_date]), #duration(1,0,0,0)), each Date.StartOfMonth(_)))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "new total", each [total]/List.Count([dates])),
    #"Expanded dates" = Table.ExpandListColumn(#"Added Custom1", "dates")
in
    #"Expanded dates"

[Anonymous]

Sorry, I'm very new to this. How do I use this in my situation, assuming the file has been uploaded to PowerBI

[Stachu · Accepted solution]

in Query editor go to Advanced view

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck/NV9JRMjIwtNAzMAQiOMdYzxjEMVOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [classes = _t, start_date = _t, end_date = _t, total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"classes", type text}, {"start_date", type date}, {"end_date", type date}, {"total", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "dates", each List.Distinct(List.Transform(List.Dates([start_date], Duration.Days([end_date]-[start_date]), #duration(1,0,0,0)), each Date.StartOfMonth(_)))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "new total", each [total]/List.Count([dates])),
    #"Expanded dates" = Table.ExpandListColumn(#"Added Custom1", "dates")
in
    #"Expanded dates"

copy the blue syntax while also replacing the red reference to the one that will be there in your query
you will also have to remove the two bottom rows from your original query

[Anonymous]

Tried again and it worked. You're awesome 😄

[Anonymous]

Thanks alot. This has worked half way so far , however, it did not divide the "Total" into equal parts. Is there a way, this could be done? Thanks alot again. This is the closest I've gotten to the final goal.