Forum Discussion

Krankyboy's avatar
Krankyboy
New Member
1 year ago

Looping through a table to find a vaue

Hi 

 

I want to pass into a function a date and number, (the number of workdays a task will take) from my fact table.  I have a second table which is  just rows of workdays and a second column with a value of 1 - this signifies that the associated date is a workday.  Any dates falling on a weekend or stat holiday have been removed.

 

Basically I want to loop N times through the table and return the date.

 

Thanks,

 

A

Need Help

5 Replies

  • slorin's avatar
    slorin
    Super User
    1 year ago

    Hi Krankyboy 

     

    Workdays_Table[Workday_Column]{List.PositionOf(Workdays_Table[Workday_Column], [date]) + [number] }
     

    Stéphane

    • Krankyboy's avatar
      Krankyboy
      New Member
      1 year ago

      Hi,

       

      Thanks for the response.  But this is not the answer.  I should have added more info.

       

      my workday table is just rows like the following;

      date                 isWorkDay (always 1)

      2025-01-02    1

      2025-01-03    1

      2025-01-06    1

      2024-01-07    1

       

      I want to pass in a task start date, say 2025-01-01 which takes 3 days,  I assume I would have to loop 3 times then return 2025-01-06.   That returned date will become the start date for the next task.  My fact table will have about 12 task starts per row along with 12 workdays.

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Community Champion
        1 year ago

        =Table.ExpandListColumn(Table.AddColumn(YourFactTable,"Workday Date",each List.FirstN(Table.Skip(WorkdayTable,(x)=>x[Date]<[Date])[Date],[Number])),"Workday Date")

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Krankyboy ,

     

    Please try new a custom function:

    let
    GetEndDate = (startDate as date, workDays as number) =>
        let
            filteredWorkDays = Table.SelectRows(Table, each [date] >= startDate),
            workDayList = List.Distinct(Table.Column(filteredWorkDays, "date")),
            requiredWorkDays = List.FirstN(workDayList, workDays),
            endDate = List.Last(requiredWorkDays)
        in
            endDate
in
    GetEndDate

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum