Refresh Excel Data

[Anonymous]

Hi,

I'm working with HR data that was given to me in an Excel spreadsheet. Some of the employees listed in the spreadsheet need to be removed from the dataset. I know that I can simply delete their names from the spreadsheet before uploading the data to Power BI desktop, but I want to make sure that when we need to refresh the data, those names aren't added back into the data set with a new spreadsheet.

Most likely they won't be removed from the HRIS since we do not have a full-time HRIS administrator. So if someone runs a report from the HRIS, these employees will be in the new spreadsheet again. I will need to make sure they aren't added back in when someone pastes a new spreadsheet into the Excel workbook.

If I can remove the employees in Power Query Editor, will Power BI know to keep them removed if the Excel spreadsheet is updated? Or will they be added back in?

Thank you!

[Anonymous]

Hi @Anonymous ,

You can't directly delete rows in Query Editor, you will need to relplace the rows to null then use remove empty feature. In this case, as long as the employees' names or ids didn't change, you could keep them removed. But in this way need to manually replace the rows to empty one by one, if you have plenty of rows to delete the workload will be huge. If you want to avoid replacing rows one by one, you may create a new table list the removed employees and use merge queries feature with Anti join.

https://docs.microsoft.com/en-us/power-query/merge-queries-left-anti 

Best Regards,

Jay

[_sfrost]

@Anonymous 

If your employees have a unique id (or any column you can rely on as unique) in the spreadsheet, you can make use of that column and filter out the employees you don't need. So, that way, Power BI will know to remove those employees from your data and you don't have to worry about them appearing in the dataset even after refresh.

Filter by values in a column | Microsoft Docs

Did I answer your questions? Give a thumbs up and accept this post as solution.

[aj1973]

Hi @Anonymous 

You can use Power Query to replace those names by empty Cells, however the rows will keep coming back in the desktop, it is just the names that are going to be empty. Also if the Rows are in a specific range all the time then you can remove them in power query too.

[Anonymous]

Thanks for your reply, @aj1973 

Do you have an in-depth article that explains your solution? I'm still a bit confused.

[aj1973 · Accepted solution]

Sorry I don't recall an article for this matter.

What's confusing you?

How about adding a list of only active employees and use it in your report? I think it's better this way