none
Convert a "List" record to a "Table" record without Expanding the List RRS feed

  • Question

  • Hi all!

    I am trying to generate some missing dates in a table with several "groups" of data.  Each group of data has transactions from a start date to a finish date.  I want to add the missing dates to each group's data before expanding the group.  The approach I took (I am open to a better solution if anyone has a bright idea...):

    First I grouped the data by the groups (Resource ID) and get the Min date (Start) and Max Date (Finish).  I also added a column of "all rows" so I still have the original table.  Here's what that result was:

    Notice in the sample, the dates 9/14 and 9/15 are missing as well as 9/21 & 9/22...  

    Next, I added a column that generated a list of dates from the start date to the finish date.  Wonderful!  I now have my missing dates.  I just need to Combine the two tables and delete the duplicates...   Here's a sample:

    Now, I just wanted to add another column that was the the combined tables (OriginalTable and ListOfDates).  This is where the wheels start coming off.  I tried adding a column with Table.Combine as follows:

    Turns out, you can't Combine a Table and a List.  OK that makes a bit of sense... So, how to convert the List to a Table?  I tried this:

    = Table.AddColumn(AddField_ListOfDates, "TableOfDates", each Table.FromList([ListOfDates], null, {"Date"}))

    Sweet!  I got my Tables.  But not so fast... Every "list of dates" is just returning an Error. :-(

    

    So, two questions on which I am hoping to get some help.

    1. What's the magic trick to getting this list into a table without expanding the list?  Is it even possible?

    2. Assuming we can convert the list of dates to a table of dates.  Will the Table.Combine step I originally tried work?  If so, great.  If not, what would be the better approach.

    = Table.AddColumn(#"Added Custom1", "CombinedTables", each Table.Combine({[OriginalTable], [TableOfDates]}))

    Thanks so much for the help... 

    John


    John Thomas

    Friday, October 25, 2019 11:36 PM

Answers

  • You may slightly modify the code:

    let
        group = Table.Group(ChangeTypes, {"Resource ID"}, {"temp", each
                  [a = List.Min([Date]),
                   b = List.Max([Date]),
                   c = List.Difference(List.Generate(()=>a, each _ <= b, each Date.AddDays(_,1)), [Date]),
                   d = Table.FillDown(Table.Sort(_ & #table({"Date"}, List.Zip({c})), {"Date", 0}), {"Resource ID"})]}),
        expand = Table.ExpandRecordColumn(group, "temp", {"a", "b", "d"}, {"Start", "Finish", "TableWithAllDates"})
    in
        expand

    • Marked as answer by jbt_PwrPvt Wednesday, October 30, 2019 9:54 PM
    Tuesday, October 29, 2019 9:36 PM

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Add missing dates.
    All GUI's. No tricks.
    http://www.mediafire.com/file/343tp5y06v5y1w0/10_25_19.xlsx/file
    http://www.mediafire.com/file/dklk1d45ivqcshr/10_25_19.pdf/file

    Saturday, October 26, 2019 3:17 AM
  • Herbert,

    Thanks so much for taking the time to craft a possible solution.  Unfortunately, this doesn't solve the question I asked by assuming something about the data that isn't true.  Each resource has a start date and finish date and I need to add the missing dates between JUST those two dates.  For example, you can see that Resource ID "50-PFWJ" starts on 9/9/2019 and ends on 12/26/2019 so i need to generate missing dates for 50-PFWJ just between those two dates.  Ditto for the other resources. 

    Your approach is one I considered, using my Calendar table, but the issue is that this data set is a bit missleading in that all resources are on what we call 5/8's, meaning 5 days a week, 8 hours a day.  We also have 4/10's, 5/10's, 6/10's, 6/12's, 7/10's, and even 7/12's.  It's not uncommon for us to start a job on 4/10's for planning phase, move into 5/10's for Pre-Outage phase, and then work 7/10's or 7/12's during the outage, and then back to 4/10,s for Post-outage work.

    For this reason, i need a solution that sticks to the parameters I outlined in my original problem.  Sorry I didn't dive into the complexities of the situation.  I kept it simple to focus on the situation at hand...

    Thanks again,

    John

    PS: While I wish it were not so, our organization is a bit behind the times.  We use Excel 2013... :-(


    John Thomas


    • Edited by jbt_PwrPvt Sunday, October 27, 2019 9:33 PM
    Sunday, October 27, 2019 9:27 PM
  • Thanks for dribbling out the complexities of the situation.
    Pass.

    Sunday, October 27, 2019 10:42 PM
  • Table.FromColumns( { YourList } ) will transform the list to a table.

    Not sure if I understood your request correctly, but I guess that merging both tables with the new Dates-table on the outer-side should give the desired results.

     


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, October 28, 2019 6:48 AM
    Moderator
  • Hello

    if I got you right you want to create a list-output with the missing dates of the resource assignment

    try to add a new column with the function below - creates a list with the start to end of the resource assignment and then checks the difference to the assigned days. The output is a list with the missing days

    List.Difference(List.Dates(Date.From([Start]),Duration.Days([Finish]-[Start]), #duration(1, 0, 0, 0)),Table.Column([OriginalTable],"Date"))

    hope it helps

    Monday, October 28, 2019 9:47 AM
  • Hi,

    Instead of your GroupToResAndCal step:

    let
        group = Table.Group(ChangeTypes, {"Resource ID"}, {"temp", each
                  [a = List.Min([Date]),
                   b = List.Max([Date]),
                   c = List.Difference(List.Generate(()=>a, each _ <= b, each Date.AddDays(_,1)), [Date]),
                   d = Table.Sort(_ & #table({"Date"}, List.Zip({c})), {"Date", 0})]}),
        expand = Table.ExpandRecordColumn(group, "temp", {"a", "b", "d"}, {"Start", "Finish", "TableWithAllDates"})
    in
        expand
    Monday, October 28, 2019 1:23 PM
  • Aleksei!!! That is exactly what I needed.  (Any way to "Fill Down" the "Resource ID", field before I expand?  Just want to be sure to keep the dates with the correct resources when I expand the table.  Here's screen shot of what it generated:


    John Thomas

    Tuesday, October 29, 2019 9:05 PM
  • I tried this and it doesn't work...


    John Thomas

    Tuesday, October 29, 2019 9:09 PM
  • You may slightly modify the code:

    let
        group = Table.Group(ChangeTypes, {"Resource ID"}, {"temp", each
                  [a = List.Min([Date]),
                   b = List.Max([Date]),
                   c = List.Difference(List.Generate(()=>a, each _ <= b, each Date.AddDays(_,1)), [Date]),
                   d = Table.FillDown(Table.Sort(_ & #table({"Date"}, List.Zip({c})), {"Date", 0}), {"Resource ID"})]}),
        expand = Table.ExpandRecordColumn(group, "temp", {"a", "b", "d"}, {"Start", "Finish", "TableWithAllDates"})
    in
        expand

    • Marked as answer by jbt_PwrPvt Wednesday, October 30, 2019 9:54 PM
    Tuesday, October 29, 2019 9:36 PM
  • Excellent.  Thanks so much for the hand.

    John Thomas

    Wednesday, October 30, 2019 9:54 PM