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.
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago
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.