A dashboard I created in MS Excel
The data used is fictitious.
Dashboard
The data is linked to the “Calculations” sheet to display the latest information.
Calculations
Pivot Charts created from the data connections provided the data for the dashboard. The Pivot Charts were created with data connections from Power Query transformations to make sure the calculations are dynamic, applied the desired logic and automatically update when new information is added to the “Sales” file.
Using Power Query Conditional Columns to solve a hacking scenario
Merging Tables
Scenario: possible discrepancies have been detected in the current Salary file, luckily you have a back up of the Salary file. Using Power Query import the tables, transform then merge them. Use the merged columns to look for any discrepancies in salary.
Conditional Column
Once the data is merged, create a conditional column to compare the back up data with the current data. The logic I used was basically if “current” does not equal “Backup” then “Altered”, else “Unaltered”.
Results
I filtered the Conditional Column to remove the unaltered rows, added a column to calculate the salary difference between the current and backup file then loaded the results to an Excel sheet. The results, four salaries were altered and two employees were deleted.
Make a report with the help of Power Query
Using the source data from the Employee Data tab, create a report that includes the following: Dept, Position, First & Last Name, and Salary of the employees that earn a salary above the threshold listed. Sort in ascending order by department, then salary. Ensure it's fully dynamic. If new data is added, the values in the resulting table will be updated accordingly once refreshed.
The data used is fictitious.
