Forum Discussion
Iterate through all my dataset to select values
Hi guys!
I'm looking forward to create a dashboard with data from ServiceNow.
I am extracting around 130 MB, around 30k rows and 180 columns, this information is about incidents, CSV file.
The data has duplicated incidents (some more than twice), so I need to iterate through each row and look at different columns to see if some values meet my conditions.
Example:
| Incident ID | Priority | Created in |
| 123452643 | High | 07/01/2020 08:23:12 a. m. |
| 123452643 | High | 08/01/2020 02:57:24 a. m. |
Here I need to evaluate which incident was created first and pick that row, so in this example I'd be chosing first row, and need to get rid of the second row for the same incident.
My main goal here is to clean up the data by only leaving one row per incident (which lowers the CSV file from 30k rows to 10k rows), the one that meet my conditions.
I assume I need to use loops and conditions.
Is this too much for Power BI?
If not, should I do it with M or DAX?
How is this going to hit the performance while refreshing?
Is it advisable to use Power BI for this kind of clean up?
I was thinking in doing a pre-processing with python to eliminate duplicated values and then getting the freshly cleaned up CSV file into Power BI.
All suggestions are welcome!!
Thanks in advance guys.
OttmarV
Hi Anonymous ,
You can use the GROUP BY function in Power Query. Please refer the following screenshot.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
camargos88Community Champion
Anonymous ,
You can do it using Power Query, so you keep only what you need.
Performance depends on how you write the mcode.
Power Query is used to prepare/model/transform data.
DAX to analyze data, sometimes is necessary to model using DAX, but it's prefered using Power Query.
v-zhenbw-msftCommunity Support
Hi Anonymous ,
You can use the GROUP BY function in Power Query. Please refer the following screenshot.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply and sorry for the late response, I wasn't working on the project yet.
What I needed to perform, was a row operation after the Group By. I had several rows and columns of information for a single incident, so I needed to consolidate all the rows into a single one, based on different column conditions. This might be hard to explain, but here's the documentation that helped me go through, I hope someone might find this helpful.
Thanks!!
Ottmar V