Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello guys! I have a problem...hope you can help me.
We have a process in which every step is inserted in the database. The goal is to know how many days the process stuck in a particular status, by comparing the ID number of the application. Like this:
I need the days column in power bi, because I need to calculate an average per process step.
I've tried several solutions that I found on this forum (trying editing in M, adding index, loockup values, etc) but I cannot get the result I need. Any help would be appreciated
Thank you!
Here's a data sample if needed:
Application ID | Application Status | Created Date | Days |
281 | Default | 1/6/2015 | 1 |
281 | Withdrawn By Candidate | 1/7/2015 | 880 |
301 | Default | 1/8/2015 | 11 |
301 | CV Review | 1/19/2015 | 37 |
301 | DQ CV Review | 2/25/2015 | 831 |
321 | Default | 1/7/2015 | 12 |
321 | CV Review | 1/19/2015 | 29 |
321 | DQ CV Review | 2/17/2015 | 839 |
341 | Default | 1/7/2015 | 0 |
341 | CV Review | 1/7/2015 | 880 |
362 | Default | 1/7/2015 | 12 |
362 | CV Review | 1/19/2015 | 29 |
362 | DQ CV Review | 2/17/2015 | 839 |
381 | Default | 1/9/2015 | 10 |
381 | CV Review | 1/19/2015 | 37 |
381 | DQ CV Review | 2/25/2015 | 831 |
401 | Default | 1/9/2015 | 10 |
401 | CV Review | 1/19/2015 | 18 |
401 | DQ Other Reasons | 2/6/2015 | 850 |
421 | Default | 1/12/2015 | 7 |
421 | CV Review | 1/19/2015 | 868 |
Solved! Go to Solution.
Solution in Power Query (M):
let Source = Table1, #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter), #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Application_ID", "Created_Date"}, {"Next.Application_ID", "Next.Created_Date"}), #"Added Custom" = Table.AddColumn(#"Expanded Next", "Days", each Duration.Days(if [Next.Application_ID] = [Application_ID] then [Next.Created_Date] - [Created_Date] else DateTime.Date(DateTime.LocalNow())-[Created_Date]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Next.Application_ID", "Next.Created_Date"}) in #"Removed Columns"
So that I understand this, if there is another date for an Application ID, then a status change occurred and you want the difference between this next date and that date. Otherwise you want the difference between that date and today?
Thats right. If the date changes, but the application doesn't, a status change occured.
The data is sorted on Application ID, so we can be sure that when the ID changes, the previous row was the last status of the prior ID.
Well, that looks like a job for EARLIER but to be honest, I'm not that very good with EARLIER.
https://msdn.microsoft.com/en-us/library/ee634551.aspx
Solution in Power Query (M):
let Source = Table1, #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter), #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Application_ID", "Created_Date"}, {"Next.Application_ID", "Next.Created_Date"}), #"Added Custom" = Table.AddColumn(#"Expanded Next", "Days", each Duration.Days(if [Next.Application_ID] = [Application_ID] then [Next.Created_Date] - [Created_Date] else DateTime.Date(DateTime.LocalNow())-[Created_Date]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Next.Application_ID", "Next.Created_Date"}) in #"Removed Columns"
MarcelBeug, thank you so much!
This is what I needed, I mean, I'm still trying to really understand what you did, but this is amazing!
Thanks again 🙂
In general if you add 2 indices, one 0-based and the other 1-based, and use these to merge the table with itself, you get in the nested tables the values:
Notice: if you adjust the generated code from the merge step and adjust "NewColumn" to "Previous" or "Next", then you have clear field names if you expand the required fields from this column and make sure to use the current column name as prefix.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |