Solved: Find min date if null based on multiple columns

Redders · ‎11-10-2022

Hello Community,

I am having difficulty with trying to transform my data and to be honest, I'm not sure what to google to find the answer. I'm hoping that someone will at least be able to point me in the right direction.

A bit of background, I have been asked to produce a report that shows the progress of orders through a workflow. The datasource (API) only provides a snapshot of the orders at that moment. My solution was to use power automate to update a spreadsheet daily using the results from the API to create a historical dataset. I have then created a workbook that connects to that spreadsheet to obtain the data. This works fine for providing a few views of the data on any given day (only started a few days ago). Each day I add TodaysDate then add the rest of the data in each of the respective columns.

The problem that I am encountering is the next requirement. The ask is to show dwell time of an order at a given status. I don't think it would be an issue if I had been collecting the data since the beginning however, I have only been collecting for a few days.

Each order runs through the work flow starting from New through 9 statuses to Invoiced.

New	1
Accepted	2
Survey	3
Unscheduled	4
Scheduled	5
In Progress	6
Engineering Completed	7
Complete (Not Invoiced)	8
Complete (Invoiced)	9

The dates that I have available in the dataset are 'Received' = New, 'Eng Comp Sent Date' = Engineering Completed and 'Completed Date' = ReadyforInvoicing.

My initial thoughts were to get a list of all the estimates and available statuses and populate with the date that it was created either using the TodaysDate (when adding the data) if the data is available for that status, ReceivedDate if it hadn't been Engineering Completed or the CompletedDate if it was ready for invoicing. I have tried to start this but I am completely lost on how to get to the next step

This is an example however, as you can probably imagine, some of the orders have other columns/rows populated as well

If someone can just give me a few words of advise (tell me that I'm going about it in the complete wrong way or what I have done right), I would really appreciate it.

Thanks in advance

Redders · ‎11-11-2022

Hi @v-yanjiang-msft,

Sorry if it was unclear, I was getting a little frustrated yesterday and couldn't see the problem clearly. What I was looking for was the age of the order at a given status.

After having a sleep on it, I think I've worked out my biggest issue. I couldn't understand how I would determine whether the today's date (added on data capture) would be the place to determine the age of the status or whether to just use the received date for statuses before engineering completed.

What I have been able to work out is that I won't be able to accurately determine the age of an order at a given status unless the recieved date is greater than to equal to the 07/11/2022 (when I started to capture the data).

What I have done to resolve this is to create a new table from the data, group by the orderID, received date, eng comp date, completed date and status. I also aggregated DateToday as min so that it obtained the earliest date that it appeared in the data for that given status and called that FirstOnData.

I then created a new column

= Table.AddColumn(#"Changed Type", "Age", each if [Status] = "Complete (Not Invoiced)" then Duration.Days([TodaysDate] - [Completed Date]) else if [Status] = "Engineering Completed" then Duration.Days([TodaysDate]-[Eng Comp Sent Date]) else if [Received] < #date(2022,11,7) then Duration.Days([TodaysDate] - [Received]) else Duration.Days([TodaysDate] - [FirstOnData]))

This allows me to utilise the appropriate columns for eng comp and completed and also the DateToday if I have the data to use, if not it reverts to the recieved date to calculate the age

I hope this makes sense now and thank you for replying however, my issue is now resolved

Thanks

View solution in original post

Redders · ‎11-11-2022