Transform a table using Power Query

[EduardG]

Hello,

I am trying to convert a table that data stored in this way:

Name	Start	End
Anna	01-Jan-2024	15-Mar-2024
Mike	16-Feb-2024	30-Apr-2024
Sean	01-Mar-2024	31-Mar-2024

to look like this:

Name	Period
Anna	Jan-2024
Anna	Feb-2024
Anna	Mar-2024
Mike	Feb-2024
Mike	Mar-2024
Mike	Apr-2024
Sean	Mar-2024

It's basically for each name I want to have a row with the months when the person is in. Thanks for any suggestions how can it be done.

[SundarRaj]

Hi @EduardG , another solution you can possibly look at. Thanks!

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type date}, {"End", type date}}),
Rows = List.Transform(Table.ToRows(#"Changed Type"), each List.Select(_, each not (try Date.From(_))[HasError])),
Numbers = List.Transform(Rows, each List.Transform(_, each Number.From(_))),
Dates = List.Transform(Numbers, each List.Transform({_{0}.._{1}}, each Date.From(_))),
List = List.Transform(Dates, each _),
Date = Table.TransformColumns(Table.AddIndexColumn(#"Changed Type"[[Name]],"Date",0,1),{"Date", each List{_}}),
Months = Table.AddColumn(Date, "Months", each List.Distinct(List.Transform(_[Date], each List.Transform({Text.Start(Date.MonthName(_),3), Date.Year(_)}, each Text.From(_))))),
Tables = Table.TransformColumns(Months,{"Months", each List.Transform(_, each Text.Combine(_,"-"))}),
Expand = Table.ExpandListColumn(Tables, "Months")[[Name],[Months]]
in
Expand

[EduardG]

Thanks @SundarRaj. This is an interesting idea, but I prefer the column Month to be in date format. I can then apply a custom format to display only month and year. Working with text is a bit tricky.

[Cookistador · Accepted solution]

The following M code will add the missing month

List.Generate(
    () => Date.StartOfMonth([Start]),
    (current) => current <= [End],
    (current) => Date.AddMonths(current, 1)
)

So add a Custom column and past this code in your column, then you just have to format it and removed the start and end date

This is the complet code I used

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszLS1TSUTIw1PVKzNM1MjAyAfIMTXV9E4sgvFidaCXfzOxUkLCZrltqEkyRsYGuYwGSouDUxDyISXC9QEVIvNhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Generate(
    () => Date.StartOfMonth([Start]),
    (current) => current <= [End],
    (current) => Date.AddMonths(current, 1)
)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Date.ToText([Custom], "MMM - yyyy", "en-US")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Start", "End", "Custom"})
in
    #"Removed Columns"