Power BI dashboard maintenance to track down COVID-19 PPE (Personal Protective Equipment) orders among all of Ontario Ministries. Power Query and DAX maintenance and customization to fit specific client needs. Recommendations on process improvements and data accuracy; Creation of macros to detect and correct data discrepancies among sources. SharePoint management.
Situation and Task
Ontario Government Ministries needed to visualize how effectively suppliers of Personal Protective Equipment (PPE) were performing. Since most ministries had different requirements for their visualizations, each dashboard needed to be customized to accommodate those specific requirements.
In addition to that, the data was very differently sourced and inconsistent, even with data entered with typos.
Action
After a series of meetings with the stakeholders, lists of specifics KPIs were created and the dashboards were updated accordingly to meet each Ministries’ needs, by editing SQL queries and DAX formulas.
VBA Macros were created to detect and fix data inaccuracies, the code was constantly being updated as more discrepancies were found after data reconciliation. The master data file was hosted in a SharePoint data reposiotory for easy access to dashboards and stakeholders.
Results
Ministries were able to address delivery issues by contacting the supplier in a timely manner with very precise information when a supplier’s poor performance was detected. Users were able to track single orders and address the situation for a specific shipment with extreme accuracy.