How to Convert New Sales Territory/Rep Data Table with Single Date "Event" Rows

[zwyatt]

Hitting brick wall and reaching out while trying to fully convert a new Territory / Salesperson Assignment table in Power Query.  

Was given a new SQL "Territory Assignment" table only containing initial 'event' rows with single date (for each particular Territory / SalesRep combination), such as when a territory is first assigned a rep OR if a new rep replaces another. 

- If a territory has had only one rep (like Territory 1), then there is only one initial date row.  

I'm needing a way to modify this single date 'event' row table by inserting add'l rows for every date (for each territory/salesrep combo) after or between what's given.  

**  This is an over-simplified example of what I'm needing to accomplish, for the actual table. 

A few "example" rows of the current report showing the 2 types of territory/rep 'events':

TerritoryId	EmployeeId	StartDate
1	345	6/1/2025
1	789	6/5/2025
2	4545	6/3/2025

I'm needing a way to CONVERT that format, into this:

TerritoryId	EmployeeId	StartDate
1	345	6/1/2025
1	345	6/2/2025
1	345	6/3/2025
1	345	6/4/2025
1	789	6/5/2025
1	789	6/6/2025
1	789	6/7/2025
1	789	6/8/2025
1	789	6/9/2025
1	789	6/10/2025
2	4545	6/3/2025
2	4545	6/4/2025
2	4545	6/5/2025
2	4545	6/6/2025
2	4545	6/7/2025
2	4545	6/8/2025
2	4545	6/9/2025
2	4545	6/10/2025

The solution would be to generate add'l row for each day between start and replace dates, and IF only ONE rep has been assigned, then a row for each date through yesterday.  

** Here is a sample blank PBI report with a data table containing large sample portion of the new table.

TerritoryEmployeeDate Example.pbix 

[v-pnaroju-msft · Accepted solution]

Thank you, @ZhangKun, @SundarRaj and @ronrsnfld, for your responses.

Hi @zwyatt,

We would like to check if the solution provided by @ZhangKun, @SundarRaj, @ronrsnfld has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.
If you found the response helpful, please mark it as the accepted solution. This recognition benefits other members seeking solutions to similar queries.

Thank you.

[zwyatt]

Hey guys. Apologies for the delay.  

Yes! The original solution posted by @ZhangKun worked!  All I had to adjust was the End Date variable from a static date, to a dynamic pull for Prior Day's date.  
Also, a few minutes of trial and error to swap in my actual data source and add'l columns.  

Other than those, please consider it solved.  

I haven't had a chance to review the other submissions, besides a cursory "skim" over, but will dig into them today, to see if there's improvement in elegance or efficiency.  

Thanks again!

[ronrsnfld]

Another Method:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI2MQWSZvqG+kYGRqZKsToQYXMLS7CwKULYCChgYgpVbgwVjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TerritoryId = _t, EmployeeId = _t, StartDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"TerritoryId", Int64.Type}, {"EmployeeId", Int64.Type}, {"StartDate", type date}}),

//Could be today or any other date
/* Because of the subtraction to get Duration.Days, setting this
    as today will result in the proper calculations below*/
    Yesterday=Date.From(DateTime.FixedLocalNow()),

#"Grouped Territory" = Table.Group(#"Changed Type", {"TerritoryId"}, {
    {"Dates", (t)=> [a=List.Generate(
                        ()=>[a=List.Dates(t[StartDate]{0},
                                        Duration.Days(
                                            ((t[StartDate]{1}?)??Yesterday)-
                                            t[StartDate]{0}),
                                        #duration(1,0,0,0)),idx=0],
                        each [idx] < Table.RowCount(t),
                        each [a=List.Dates(t[StartDate]{[idx]+1},
                                        Duration.Days(
                                            ((t[StartDate]{[idx]+2}?)??Yesterday)-
                                            t[StartDate]{[idx]+1}),
                                        #duration(1,0,0,0)),idx=[idx]+1],
                        each[a]),
                     b=Table.FromColumns(
                          {t[EmployeeId]}
                         & {a}, type table[EmployeeId=Int64.Type,StartDate={date}]),
                     c= Table.ExpandListColumn(b,"StartDate")][c],
                            type table[EmployeeId=Int64.Type, StartDate=date]
                  }}),
    #"Expanded Dates" = Table.ExpandTableColumn(#"Grouped Territory", "Dates", {"EmployeeId", "StartDate"})
in
    #"Expanded Dates"

Source

Results

[SundarRaj]

Hi @zwyatt, another solution I could work out using Power Query. Thanks. I'll attach the file link. Have a look if that is what you intend to do with your query.
https://docs.google.com/spreadsheets/d/1a3q1IxlqHrM_Jgajq6B0zOkyni2vLFV9/edit?usp=sharing&ouid=10475...

[ZhangKun]

You can adjust dt to modify it to your desired date, or a dynamic date.

[zwyatt]

Very much appreciate the quick turnaround @ZhangKun !  I'll be back at my machine this afternoon to review what you put together and confirm the fix. 

Thanks!