Making a New Table with Data from Another Table

[Anonymous]

All,

I have a table that has numerous instances per "Event ID", but I need to figure out a formula / measure that can removed the last instance the event ID occurs based on "Time_OF_Event" , or if there is only one instance it does not count it. 

Below is a picture:

For example, "EventID" 210043140 I only want to include the first instance where "TIME_OF_EVENT" is 9:58:49. 

Another example, EventID 210043805 can be removed since it is the only instance of that "EventID".

If it is a bit vague I would be glad to provide more information in the comments.,

Any help would be greatly appreciated!

Thanks

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

You could test like below:

base table:

Step1,use the following dax to create two new column:

index = RANKX(FILTER(all('Table'),'Table'[event_idx]=EARLIER('Table'[event_idx])),'Table'[Time_of_event],,ASC,Dense)

maxindex = CALCULATE(MAX('Table'[index]),FILTER(ALL('Table'),'Table'[event_idx]=EARLIER('Table'[event_idx])))

Step2, calculatable new table :

Table2 = CALCULATETABLE('Table',FILTER('Table','Table'[index]<'Table'[maxindex]))

And if use Power Query:

base table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc27DcAgFEPRVdCrkbDN4xNWQey/RpQmDYjWOvKd05pqdYsmiIlIDOIo/VsIeKbDVtxdDdTAsztK+fLXUcJ/WK5hHt0ePrt2cusF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number = _t, Time_of_event = _t, event_idx = _t]),
    #"Grouped Rows" = Table.Group(Source, {"event_idx"}, {{"Count", each _, type table [number=nullable text, Time_of_event=nullable text, event_idx=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"number", "Time_of_event", "Index"}, {"Custom.number", "Custom.Time_of_event", "Custom.Index"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Custom.number"}, {{"maxindex", each List.Max([Custom.Index]), type number}, {"custom11", each _, type table [event_idx=nullable text, Count=table, Custom.number=text, Custom.Time_of_event=text, Custom.Index=number]}}),
    #"Expanded custom11" = Table.ExpandTableColumn(#"Grouped Rows1", "custom11", {"event_idx", "Count", "Custom.number", "Custom.Time_of_event", "Custom.Index"}, {"custom11.event_idx", "custom11.Count", "custom11.Custom.number", "custom11.Custom.Time_of_event", "custom11.Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded custom11",{"custom11.Custom.number", "custom11.Count"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each if [maxindex]=[custom11.Custom.Index] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"maxindex", "custom11.Custom.Index", "Custom"})
in
    #"Removed Columns1"

Final get:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

Thanks for your help on this! This is exactly what I needed to do. This will help me in the future when another project like this comes up.

[Fowmy]

@Anonymous 
What is the output you are expecting? Is it a table or do you need a measure to identify the row?
So you need to keep only the first instance based on the DATE TIME OF EVENT and remove others lines and remove if you find only one row. Is that what is required?

[Anonymous]

I would prefer a new table if at all possible. Once I have it as a table (if that is even possible) I need to do some further transformations and add a few calculated columns.

[Fowmy]

If you need further transformations then I need to do it in Power Query. Can you confirm if you need it in PQ or DAX ?

[Anonymous]

Yeah, I believe PowerQuery could work. Are you trying to write the code in M?

[Anonymous]

@Fowmy Whatever you need to make a separate table is totally fine with me. Thanks so much