How to split line?

[Anonymous]

Hi!

I have a table with fact and plan columns with date interval. I want to split this line into some lines and add column with month names January and February and March.

Example

plan                   fact              month

30.01.2020       06.03.2020    January

30.01.2020       06.03.2020     February

30.01.2020       06.03.2020     March

It's possible ?

[dax]

Hi @Anonymous , 

You could refer to @ImkeF 's suggestions, you also could refer to my sample for details

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDLQN9Q3hjGN9Q0NlGJ1opWSYCJG+kYwpqm+IVguGaHRFKEMKBcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type text}, {"start", type date}, {"end", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([start])..Number.From( if Date.AddMonths([end],-1)>[start] then Date.AddMonths([end],-1) else [end] )}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "month no", each Date.MonthName([Custom])),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"date", "start"}, {{"month", each Text.Combine(List.Distinct([month no]), " "), type text}, {"all", each _, type table [date=text, start=date, end=date, Custom=date, month no=text]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"end"}, {"end"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded all")
in
    #"Removed Duplicates"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF · Accepted solution]

HI @Anonymous 

yes, I've created a function for this task some time ago: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/ 

Just fill in "Month" into the 3rd parameter and you'll get the monthly figures.