Using REST API as a data source - Adding a Date column to a table using the Day Number column

[paulfink]

I need to get my API to work.

This is my first experience with APIs.

I have got all my data but some fields aren't matching together and I need some changes in order to get the dates to work.

I am using this API to make a time tracking report for my department. We all log our times to each task and project which is saved to our system, I used to make an excel with all the time tracks every month then every week but now we need an automatic report we can update anytime.

I have 6 Tables:

• Leave Time
  • Leave Time
  • Leave Type ID
  • User ID
• Leave Types
  
  • Leave Type
  • Leave Type ID
• Projects
  • Project ID
  • Project Name
• Tasks
  • Task ID
  • Project ID
  • Task
• Time Track Tasks
  • Record Time
  • Task ID
  • User ID
• Users
  • Active
  • Name
  • ID

Also, all these tables with Record Time have another column call Day Number (Originally Day Offset), this just have numbers going from 1-158 (1 being Jan 1 and 158 being today I think). I want to make a column that has a date column in it, I have set up a relationship with my calendar table and that column in the different tables but adding a column would be easier. How do I do this? All my data is from this year. 

Another problem is that the Task ID and Tasks do not much. I'm not sure how this has happened. When I search individual IDs in the Projects table, none of them match. Any ideas why this has happened? 

[v-juanli-msft]

Hi @paulfink 

Create calculated columns

month rename =
VAR m1 = 31
VAR m2 = m1 + 29
VAR m3 = m2 + 31
VAR m4 = m3 + 30
VAR m5 = m4 + 31
VAR m6 =
    m5
        + DATEDIFF (
            DATE ( 2020, 6, 1 ),
            TODAY (),
            DAY
        ) + 1
RETURN
    SWITCH (
        TRUE (),
        [day number] <= m1, 1,
        [day number] <= m2, 2,
        [day number] <= m3, 3,
        [day number] <= m4, 4,
        [day number] <= m5, 5,
        [day number] <= m6, 6
    )

day rename =
VAR m1 = 31
VAR m2 = m1 + 29
VAR m3 = m2 + 31
VAR m4 = m3 + 30
VAR m5 = m4 + 31
VAR m6 =
    m5
        + DATEDIFF (
            DATE ( 2020, 6, 1 ),
            TODAY (),
            DAY
        ) + 1
RETURN
    SWITCH (
        TRUE (),
        [day number] <= m1, [day number],
        [day number] <= m2, [day number] - m1,
        [day number] <= m3, [day number] - m2,
        [day number] <= m4, [day number] - m3,
        [day number] <= m5, [day number] - m4,
        [day number] <= m6, [day number] - m5
    )

new date = DATE(2020,[month rename],[day rename])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paulfink]

This worked! but the New Date column came back with an error on my second table:

"An argument of function 'DATE' has the wrong data type or the result is too large or too small."

However, the columns are the exact same as the other table I use this for. It worked perfectly the first time.

All the columns have Whole Number as the Data Type and Format. I even tried to change it to General as the format.

[v-juanli-msft]

Hi @paulfink 

In my solution, there is only one table.

What second table do you apply to?

Can you show me your problem and i will try my best to figure out it so to close this case?

Best Regards

Maggie