Iterate through all my dataset to select values

[Anonymous]

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

[v-zhenbw-msft · Accepted solution]

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.

[Anonymous]

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.

https://docs.microsoft.com/en-us/power-query/group-by#perform-a-row-operation-to-group-by-one-or-mor...

Thanks!!

Ottmar V

[camargos88]

@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.