Building a custom function in Power Query in Excel 2016 where each client has a order type/id. I need to find the datediff from the most recent of a New or Upgrade to the Disconnect date. Need to find the rows where there's a disconnect after a new or upgrade value.
How do I find whether the client disconnected after they had an Upgrade or New order?
If [Client] has "Disconnect" after "New" = X or Client "Disconnect" after "Upgrade"
Then, how to find the days since last update from the start [Last Update] - [Start] if [Disconnect After] = "Disconnected After New or Upgrade"
When Client only has [Order Type] = "Disconnect" then [Disconnect After] = null When Start = null then [Disconnect After] = null
Mainly had trouble figuring out how to make a conditional M query to find where if Client had a Disconnect after a New or Upgrade Order type. I tried to fix the errors by adding a try / otherwise but it kept erroring out. Wasn't sure how to make Clients with only Disconnect Order Type as null in Disconnect After New Order column.
Sample :
| Order Type | Client | Client ID | Start | Last Update |
|---|---|---|---|---|
| New | Bob | 2 | 1/02/2000 | 3/20/2001 |
| Disconnect | Bob | 4 | 3/20/2001 | 3/20/2001 |
| New | Dave | 5 | 1/3/2000 | 3/22/2001 |
| Upgrade | Dave | 21 | 1/4/2000 | 3/23/2001 |
| Disconnect | Dave | 24 | 3/23/2001 | 3/25/2001 |
| New | Dave | 28 | 3/31/2001 | 3/26/2001 |
| New | John | 21 | 1/7/2000 | 3/26/2001 |
| Disconnect | John | 32 | 3/26/2001 | 12/28/2001 |
| New | John | 18 | 4/8/2000 | 12/28/2002 |
| Disconnect | Jack | 14 | 3/2/2000 | 3/2/2000 |
| Disconnect | Jack | 20 | 3/2/2000 | 3/5/2000 |
| Disconnect | Hansel | 90 | 7/2/2001 |
Desired:
| Order Type | Client | Client ID | Start | Last Update | Disconnect After New Order | Since Last Disconnect Action |
|---|---|---|---|---|---|---|
| New | Bob | 2 | 1/02/2000 | 3/20/2001 | null | null |
| Disconnect | Bob | 4 | 3/20/2001 | 3/20/2001 | Disconnected After New or Upgrade | 444 |
| New | Dave | 5 | 1/3/2000 | 3/22/2001 | null | |
| Upgrade | Dave | 21 | 1/4/2000 | 3/23/2001 | null | null |
| Disconnect | Dave | 24 | 3/23/2001 | 3/25/2001 | Disconnected After New or Upgrade | 447 |
| New | Dave | 28 | 3/31/2001 | 3/26/2001 | null | null |
| New | John | 21 | 1/7/2000 | 3/26/2001 | null | null |
| Disconnect | John | 32 | 3/26/2001 | 12/28/2001 | Disconnected After New or Upgrade | 352 |
| New | John | 18 | 4/8/2000 | 12/28/2002 | null | null |
| Disconnect | Jack | 14 | 3/2/2000 | 3/2/2000 | null | null |
| Disconnect | Jack | 20 | 3/2/2000 | 3/5/2000 | null | null |
| Disconnect | Hansel | 90 | 7/2/2001 | null | null |
CodePudding user response：
Edited. Not sure why my date math is different from yours. I am subtracting the Start column on the disconnect row from the most recent (but prior) Start column from New/Upgrades
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Type", type text}, {"Client", type text}, {"Start", type date}, {"Last Update", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Client"}, {
{"data", each
let a = Table.AddColumn(_,"Max",(i)=>List.Max(Table.SelectRows(_, each ([Order Type] = "New" or [Order Type] = "Upgrade") and [Start]<i[Start]) [Start]), type date ),
b = Table.AddColumn(a,"Diff", each if [Order Type] = "Disconnect" then Duration.Days([Start]-[Max]) else null),
c=Table.AddColumn(b,"Disconnect After New Order", each if [Diff]=null then null else "Disconnected After New or Upgrade")
in c
, type table }
}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Order Type", "Client ID", "Start", "Last Update", "Diff", "Disconnect After New Order"}, {"Order Type", "Client ID", "Start", "Last Update", "Diff", "Disconnect After New Order"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded data",{{"Last Update", type date}, {"Start", type date}})
in #"Changed Type1"
