Create multiple rows from a single row

Welcome to the forum.

What a way to dive in.  I imagine this is example is going to cause a few headaches but here goes.

You have to get your spreadsheet into Power Query first.  Then a series of transforms should get you what you want.

Here's what it looks like on the first import:

 

Here is the advanced editor code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY3BCoAgDIZfRTwL6dSyc5OIwKCCDuL7v0bOXQw6bP/4tv9fzlIqecXlPk5h6ii6YgwfWlSWT4y7iAm3tFaGZEO6QkvN/TLyhcEOoEFXAk6TmLGJ1U3awrcRJt7zGTEKML5LYKsJnABs680w9z+IlfIC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"WEEK ENDING", type text}, {"8/3/2020", Int64.Type}, {"15/3/2020", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"", "SECTOR"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns"," ",null,Replacer.ReplaceValue,{"SECTOR"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"SECTOR"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Filled Down",{{"WEEK ENDING", "DEPT"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"SECTOR", "DEPT"}, "Attribute", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"SECTOR", Order.Ascending}, {"Attribute", Order.Ascending}, {"DEPT", Order.Ascending}})
in
    #"Sorted Rows"

 

If you import the Excel spreadsheet, you should get to what is in the picture.

If you paste the code above into the advanced editor (except the "Source = " line - because that will be different at your side), you'll be off to a good start.

Let me know how you get on..

If you start with the row you provided as being the initial table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jAwVNJRstA31jcyMDIAMoNdnUP8g0CCLiDCyMTAQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [LOCATION = _t, #"WEEK ENDING" = _t, SECTOR = _t, DEPT = _t, BUDGET = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOCATION", type text}, {"WEEK ENDING", type date}, {"SECTOR", type text}, {"DEPT", type text}, {"BUDGET", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "WeekBeforeDate", each Date.AddDays( [WEEK ENDING],  -6)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"WeekBeforeDate", type date}}),
    DaysBetween = Table.AddColumn( #"Changed Type1", 
        "DaysBetweenList", 
        (row) =>
            List.Generate(
                () => (row[WeekBeforeDate]) ,
                each _ <= row[WEEK ENDING],
                each _ + #duration(1, 0, 0, 0) 
            )
    ),
    ExpandList = Table.ExpandListColumn(DaysBetween, "DaysBetweenList"),
    #"Renamed Columns" = Table.RenameColumns(ExpandList,{{"BUDGET", "WeekBUDGET"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "DayBudget", each [WeekBUDGET] / 7)
in
    #"Added Custom1"