Writing Loops To Simplify Inserting Rows In M Language / Power Query

Question

Long time listener, first time caller, so be gentle with me.

I'm writing a 12-week rolling linear regression prediction onto pre-existing data and need a simpler way to "add on" 12 weeks of data.

The initial 12 rows show what I have so far, but what I need to do is add on 12 weeks rolling from the current week (I think I have that, aside from the <52 part) but I also need to add a week on for each of the different locations.

This is what I have so far:

= Table.InsertRows(#"Grouped Rows",0,
    {
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+1, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+2, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)), Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+3, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+4, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+5, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+6, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+7, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+8, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+9, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+10, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+11, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null],
    [Sky Week=List.Max(#"Grouped Rows"[Sky Week])+12, Early Life Vol=null, LOCATION=Text.Combine(List.Range(#"Grouped Rows"[LOCATION], 2, 1)),Total Vol=null, Headcount=null]
    })

As you can see, this is unwieldy and probably very slow. And I need to do a new line for each site. The issue is, I don't want to have to keep adding these every time a new location pops up and, if possible, I'd like it to only show sites that have data for the past 12 weeks. As some weeks, there will be no data, and - therefore - the site will not show up.

I have tried list.generate, but I cannot for the life of me get my head around it. The actual prediction part I have in DAX and that works fine, it's just generating the underlying weeks and locations I'm struggling with. Any help would be immensely appreciated.

Answer 1

It might be easier to do generate all the rows you want and then left join your data onto that.

You can generate a new table in a cross join sort of process of adding columns of lists and then expanding them like this:

let
    YearTable = Table.FromColumns({{2019,2020}}, {"Year"}),
    #"Added Weeks" = Table.AddColumn(YearTable, "Week", each {1..52}),
    #"Expanded Week" = Table.ExpandListColumn(#"Added Weeks", "Week"),
    #"Define Sky Week" = Table.AddColumn(#"Expanded Week", "Sky Week", each 100 * [Year] + [Week]),
    #"Added Locations" = Table.AddColumn(#"Define Sky Week", "LOCATION", each {"Dunfermline", "Leeds", "Livingston", "Newcastle", "Sheffield", "Stockport"}),
    #"Expanded Location" = Table.ExpandListColumn(#"Added Locations", "LOCATION")
in
    #"Expanded Location"

This gives a full list of each week for each year and each location you specify.

Then you can do Merge Queries and match on both the Sky Week and LOCATION columns (by holding down Ctrl to select more than one) and use a left outer join to bring over the Total Vol wherever it exists in the other query.