Forum Discussion

EduG's avatar
EduG
Copper Contributor
Apr 01, 2025

Transform a table using Power Query

Hi,

I have a table that looks like this:

NameStartEnd
Anna01-Jan-202415-Mar-2024
Mike16-Feb-202430-Apr-2024
Sean01-Mar-202431-Mar-2024

which I would like to transform into this:

NamePeriod
AnnaJan-2024
AnnaFeb-2024
AnnaMar-2024
MikeFeb-2024
MikeMar-2024
MikeApr-2024
SeanMar-2024

I suspect doing it in Power Query shold be the easiest, but I don't have much experience with that. Thanks for your help.

excel
power bi

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 02, 2025
    let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Benutzerdefiniert", each List.Distinct(List.Transform({Number.From([Start])..Number.From([End])}, each Date.StartOfMonth(Date.From(_))))),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "Benutzerdefiniert"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded {0}",{{"Benutzerdefiniert", "Period"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Start", "End"})
in
    #"Removed Columns"

    This code returns the intended result in my sample file. For column Period in the green result table i've selected costum format MMM YYYY.

     

Resources