Forum Discussion

zwyatt's avatar
zwyatt
Frequent Visitor
1 year ago
Solved

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

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...
Need Help
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    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.

ronrsnfld's avatar
ronrsnfld
Super User
1 year ago

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