Forum Discussion

abhijeetdt123's avatar
abhijeetdt123
Regular Visitor
3 months ago
Solved

Power Query – Expand start week into weekly rows

Hi all,

I have a table where each row represents a project with a Start week (YYYY‑WW) and a number of weeks.

Source table:

 

Required output:

I want to expand this so that each project has one row per week, from the start week to the end week, while repeating the parameter value.

Constraints:

  • Power Query only (no DAX)
  • Weeks should be generated automatically

What is the recommended Power Query approach to generate and expand these weeks?

Need Help
  • Natarajan_M's avatar
    Natarajan_M
    3 months ago

    Hi abhijeetdt123 ?

    is this what you are looking for ?





    Link :
    Resuts Expanded.pbix

    let
    Source = #"Input table",
    ChangedTypes = Table.TransformColumnTypes(Source, {
        {"Project", type text},
        {"Start week", type text},
        {"No. of weeks", Int64.Type},
        {"End week", type text},
        {"Parameter", type text}
    }),
    AddWeekList = Table.AddColumn(ChangedTypes, "Weeks", each {0..[#"No. of weeks"]}),
    ExpandedWeeks = Table.ExpandListColumn(AddWeekList, "Weeks"),
    AddWeekColumn = Table.AddColumn(ExpandedWeeks, "Week", each
        let
            StartYear = Number.FromText(Text.Start([Start week], 4)),
            StartWeek = Number.FromText(Text.End([Start week], 2)),
            TotalWeek = StartWeek + [Weeks],
            Year = StartYear + Number.IntegerDivide(TotalWeek - 1, 52),
            WeekNum = Number.Mod(TotalWeek - 1, 52) + 1
        in
            Text.From(Year) & "-" & Text.PadStart(Text.From(WeekNum), 2, "0")
    ),
    FinalTable = Table.SelectColumns(AddWeekColumn, {"Project", "Week", "Parameter"})
in
    FinalTable



    Thanks 

    If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

    For more Power BI tips and discussions, let’s connect on LinkedIn:
    https://www.linkedin.com/in/natarajan-manivasagan

    Cheers!

     

3 Replies

  • Natarajan_M's avatar
    Natarajan_M
    Super User
    3 months ago

    Hi abhijeetdt123 ?

    is this what you are looking for ?





    Link :
    Resuts Expanded.pbix

    let
    Source = #"Input table",
    ChangedTypes = Table.TransformColumnTypes(Source, {
        {"Project", type text},
        {"Start week", type text},
        {"No. of weeks", Int64.Type},
        {"End week", type text},
        {"Parameter", type text}
    }),
    AddWeekList = Table.AddColumn(ChangedTypes, "Weeks", each {0..[#"No. of weeks"]}),
    ExpandedWeeks = Table.ExpandListColumn(AddWeekList, "Weeks"),
    AddWeekColumn = Table.AddColumn(ExpandedWeeks, "Week", each
        let
            StartYear = Number.FromText(Text.Start([Start week], 4)),
            StartWeek = Number.FromText(Text.End([Start week], 2)),
            TotalWeek = StartWeek + [Weeks],
            Year = StartYear + Number.IntegerDivide(TotalWeek - 1, 52),
            WeekNum = Number.Mod(TotalWeek - 1, 52) + 1
        in
            Text.From(Year) & "-" & Text.PadStart(Text.From(WeekNum), 2, "0")
    ),
    FinalTable = Table.SelectColumns(AddWeekColumn, {"Project", "Week", "Parameter"})
in
    FinalTable



    Thanks 

    If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

    For more Power BI tips and discussions, let’s connect on LinkedIn:
    https://www.linkedin.com/in/natarajan-manivasagan

    Cheers!

     

  • lbendlin's avatar
    lbendlin
    Super User
    3 months ago

    How is this supposed to work when a project spans across year boundaries?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 months ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {1..[No. of weeks]+1}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Text.BeforeDelimiter([Start week],"-")&"-"&Text.From([No. of weeks]+[Custom]-1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1", "Parameter", "Project"})
in
    #"Removed Other Columns"

    Hope this helps.