Forum Discussion

YavuzDuran's avatar
YavuzDuran
Icon for Helper III rankHelper III
2 years ago
Solved

Expand Table

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) ...
Need Help
  • amustafa's avatar
    amustafa
    2 years ago

    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.
amustafa's avatar
amustafa
Icon for Solution Sage rankSolution Sage
2 years ago

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.