Forum Discussion
Making a New Table with Data from Another Table
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
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 RegardsLucien
7 Replies
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?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 ?
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 RegardsLucien
