Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi Everyone
I have a datatable that I get through a power query from a SQL-server that consists of queue information for a robot that I am running.
The table looks something like this
TransactionStatus | itemReference | exceptionReason | transactionEnd |
Failed w/ Bus. Exception | 1-54-631-9 | Business Rule Exception | 27-02-2020 01:42:20 |
Failed w/ App. Exception | 1-37-71-7 | Cannot find UI element | 27-02-2020 01:42:10 |
Failed w/ Bus. Exception | 1-13-45-5 | Business Rule Exception | 27-02-2020 01:42:00 |
Failed w/ Bus. Exception | 1-13-322-1 | Error in tenant number | 27-02-2020 01:41:50 |
Failed w/ App. Exception | 1-25-404-33 | Cannot find UI element | 27-02-2020 01:41:40 |
Retried | 1-13-45-5 | 27-02-2020 01:41:42 | |
Retried | 1-23-139-2 | 27-02-2020 01:22:05 | |
Retried | 1-25-404-33 | 27-02-2020 01:21:15 | |
New | 1-25-404-33 | 26-02-2020 03:41:42 | |
Successful | 1-37-71-7 | 25-02-2020 07:22:31 | |
Deleted | 1-106-172-8 | 26-02-2020 10:30:00 | |
Successful | 1-42-100-14 | 27-02-2020 01:40:40 | |
Successful | 1-23-139-2 | 27-02-2020 01:28:32 |
I would like 2 things to happen.
First if the TransactionStatus is "Successful", then delete all other rows with the same "itemReference".
Second if a process does not have a "Succesful" then I only want to keep the one with the latest transaction date. Meaning delete all other rows with the same "itemReference" and only keep the one with the latest "transactionEnd".
Note that just because a process has been "Succesful" it can sometimes run again and fail. But if it was a success the first time and failed the second time, then I only care about the one where it succeeded. Meaning that a later fail run should also be deleted.
This would mean the table would be transformed into one looking like this:
TransactionStatus | itemReference | exceptionReason | transactionEnd |
Failed w/ Bus. Exception | 1-54-631-9 | Business Rule Exception | 27-02-2020 01:42:20 |
Failed w/ Bus. Exception | 1-13-45-5 | Business Rule Exception | 27-02-2020 01:42:00 |
Failed w/ Bus. Exception | 1-13-322-1 | Error in tenant number | 27-02-2020 01:41:50 |
Failed w/ App. Exception | 1-25-404-33 | Cannot find UI element | 27-02-2020 01:41:40 |
Successful | 1-37-71-7 | 25-02-2020 07:22:31 | |
Deleted | 1-106-172-8 | 26-02-2020 10:30:00 | |
Successful | 1-42-100-14 | 27-02-2020 01:40:40 | |
Successful | 1-23-139-2 | 27-02-2020 01:28:32 |
Any suggestions to how this can be implemented?
Thanks
Solved! Go to Solution.
See the attached PBIX file. This is what I returned using Power Query.
EDIT: I see for this image I inadvertently formated TransactionEnd as Date. Should have been DateTime. I've fixed in the PBIX link, but not redoing the image. You may only want dates in your model, not times as well, so format that however you like. But Times are necessary for this to work since some of your records are on the same day and it needs a time to find the true latest value.
Here is what I did:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous
you can group on "itemReference", select "All" in the Operations and name the group-column "ItemData".
Then add this column:
if List.Contains([ItemData][TransactionStatus], "Successful")
then [ItemData]{[TransactionStatus = "Successful"]}
else Table.SelectRows([ItemData], (x) => x[transactionEnd] = List.Max([ItemData][transactionEnd])){0}
Also see attached file
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
See the attached PBIX file. This is what I returned using Power Query.
EDIT: I see for this image I inadvertently formated TransactionEnd as Date. Should have been DateTime. I've fixed in the PBIX link, but not redoing the image. You may only want dates in your model, not times as well, so format that however you like. But Times are necessary for this to work since some of your records are on the same day and it needs a time to find the true latest value.
Here is what I did:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@ImkeF , Can you help
Hi @Anonymous
you can group on "itemReference", select "All" in the Operations and name the group-column "ItemData".
Then add this column:
if List.Contains([ItemData][TransactionStatus], "Successful")
then [ItemData]{[TransactionStatus = "Successful"]}
else Table.SelectRows([ItemData], (x) => x[transactionEnd] = List.Max([ItemData][transactionEnd])){0}
Also see attached file
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF can you explain what the following is doing?
(x) => x[transactionEnd]
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans
it's equivalent to:
each [transactionEnd]
so referencing the "transactionEnd"-field in the currently iterated row/record.
But I cannot use this syntax sugar there, as this expression is written in an "AddColumns"-window. There it will be subject to an (outer) each already. So to to make it unambigous, I've used the "standard" function definition syntax ()=>.
https://docs.microsoft.com/en-us/powerquery-m/understanding-power-query-m-functions
Another practical example here: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
109 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |