2

I am struggling with getting only the last non blank value for each ticket.
In the Power Pivot measure, I have used a function LASTNONBLANK() , but the outcome was not reliable - probably because data model does not consider the original order of rows.
Excel formula or VBA is not an option and I think that in Power Query it will not be very efficient.

ticket step timestamp value
1 1 7/28/2021 04:15 PM
1 2 7/28/2021 04:23 PM a
1 3 7/28/2021 04:30 PM
1 4 7/28/2021 04:37 PM b
1 5 7/28/2021 04:44 PM
1 6 7/28/2021 04:51 PM
1 7 7/28/2021 04:59 PM
2 1 7/29/2021 01:51 PM a
2 2 7/29/2021 02:49 PM b
2 3 7/29/2021 03:47 PM c
2 4 7/29/2021 04:44 PM
2 5 7/29/2021 05:42 PM a
2 6 7/29/2021 06:39 PM

For ticket "1", the returned value should be "b" (step 4) and for ticket "2", the returned value should be "a" (step 5).

ticket value
1 b
2 a
Capt.Krusty
  • 597
  • 1
  • 7
  • 26
MalyMajo
  • 75
  • 3

2 Answers2

2

Especially since the data is sorted, it is pretty simple in Power Query:

  • Remove the blank Value lines
  • Group by ticket number
    • Return the last Value in each group

(if it were not sorted, you would just add a sort to each subtable in the Group)

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"ticket", Int64.Type}, 
        {"step", Int64.Type}, 
        {"timestamp", type datetime}, 
        {"value", type text}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([value] <> null)),
 
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ticket"}, {
        {"Last Non-Blank", each List.Last([value])}
        })

in
    #"Grouped Rows"

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • thanks! Did not know about List.Last :) Do I understand correctly, that there is probably no way how to get same result with a measure instead of creating a new query? – MalyMajo Aug 02 '21 at 14:23
  • @MalyMajo I cannot answer that question. I do not know enough about Power Pivot and measures to be able to provide that information. I provided a PQ solution since that was one of your tags. – Ron Rosenfeld Aug 02 '21 at 16:29
  • ok, understood. I was hoping more for Power Pivot solution, because the real data source is quite huge and running another query would slow it down even more than it is now. :) I will wait few days and if nobody replies, I will mark you answer as the best one. – MalyMajo Aug 02 '21 at 16:59
0

PBI community helped me to create the Power Pivot measure with 2 different solutions, both working smoothly.

VAR _lasttimestampnonblank =
CALCULATE ( MAX ( Data[timestamp] ), Data[value] <> BLANK () )
RETURN
IF (
HASONEVALUE ( Data[ticket] ),
CALCULATE ( VALUES ( Data[value] ), Data[timestamp] = _lasttimestampnonblank )
)

and a second solution:

Measure 2 = 
    VAR __Table = FILTER('Table7',[value]<>"")
    VAR __Max = MAXX(__Table,[timestamp])
RETURN
    MAXX(FILTER(__Table,[timestamp] = __Max),[value])
MalyMajo
  • 75
  • 3