Expand Table

[YavuzDuran]

HI,

I need a help for the following scenario. 

I have a table as shown in box 1 and I am trying to add the list of all Dates (column name will be "ActiveAssignedDates" after expanding the list) in between Previous & Last Assigned Dates for each Acct ID and Assignee. 

I used the following function, but did not help me:

Table.AddColumn(#"Previous Step", "ActiveAssignedDates", each
if [Start Date] = null then
       {Number.From([Last Assigned Date])..Number.From([Today])}
else if [Last Assigned Date] > [Previous Assigned Date] and [Previous Assigned Date] <> null then
         {Number.From([Previous Assigned Date])..Number.From([Last Assigned Date])-1}
else
{Number.From([Last Assigned Date])..Number.From([Today]) }
)

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1NDc3t1TSUapwSM9NzMzRS87PBfIM9Q31jQyMTJRidZAVVaIoMsKuqApFkTFcUSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Acct ID" = _t, Assignee = _t, #"Active Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Acct ID", Int64.Type}, {"Assignee", type text}, {"Active Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ActiveDates", each List.Dates([Active Date],Duration.Days(try #"Added Index"{[Index]+1}[Active Date]-[Active Date] otherwise #duration(1,0,0,0)+Date.From(DateTime.LocalNow())-[Active Date]),#duration(1,0,0,0))),
    #"Expanded ActiveDates" = Table.ExpandListColumn(#"Added Custom", "ActiveDates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded ActiveDates",{{"ActiveDates", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Acct ID", "Assignee", "ActiveDates"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[amustafa · Accepted solution]

Hi @YavuzDuran , Using your sample input data, i would expand dates as follwing in Power Query. I have also created two measures to find curreent assigness and the date when it was assigned for each account id.

M Code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\Power BI Samples\Expand List\Sample.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Account ID", Int64.Type}, {"Current Assignee", type text}, {"Last Assigned Date", type date}, {"Previous Assignee", type text}, {"Previous Assigned Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Last Assigned Date", Order.Ascending}}),
    AddIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    AddNextDateColumn = Table.AddColumn(AddIndex, "Next Assigned Date", each try #"Sorted Rows"{[Index]+1}[Last Assigned Date] otherwise DateTime.Date(DateTime.LocalNow()), type date),
    #"Added Custom" = Table.AddColumn(AddNextDateColumn, "ActiveDates", each List.Dates([Last Assigned Date], Duration.Days([Next Assigned Date] - [Last Assigned Date]), #duration(1,0,0,0))),
    ExpandDates = Table.ExpandListColumn(#"Added Custom", "ActiveDates"),
    #"Removed Columns" = Table.RemoveColumns(ExpandDates,{"Index", "Previous Assignee", "Previous Assigned Date", "Next Assigned Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ActiveDates", "ActiveAssignedDates"}, {"Current Assignee", "Assignee"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"ActiveAssignedDates", type date}})
in
    #"Changed Type1"

DAX measures.

Current Assignee =

VAR MaxDate = CALCULATE(MAX(Table1[ActiveAssignedDates]), ALL(Table1))

RETURN CALCULATE(MAX(Table1[Assignee]), FILTER(Table1, Table1[ActiveAssignedDates] = MaxDate))

Last Assigned Date for Current Assignee =

VAR CurrentAssignee = [Current Assignee]

VAR AssigneeFirstActiveDate = CALCULATE(

    MIN(Table1[ActiveAssignedDates]),

    ALL(Table1),

    Table1[Assignee] = CurrentAssignee

)

RETURN

See the attached .pbix file for more details.