How to fill missing dates in one or multiple Power BI columns?

by

User Question: “I have a single table in Power BI with several gaps in some of the dates columns. How to complete the missing date info to ensure my time-based analysis is complete?”

Replace missing date values in Power BI

Let’s address this common issue with a straightforward Power Query solution that doesn’t require creating additional date ranges or columns.

Data Model: A single table named ‘EmployeeAttendance’ with columns: EmployeeID (Text), Date (Date), Status (Text).

Step by step instructions

  1. Open Power Query Editor:
    In Power BI Desktop, Open your report then go to ‘Home’ then go ahead and select ‘Transform’.
  1. Add Missing Dates:
  • Expand the “Date” column
  • Select the ‘Date’ column
  • Go to ‘Transform’ > ‘Fill’ > ‘Fill Values’ > ‘Fill Between’
  • Choose ‘Fill Between’ based on the Date column
  1. Handle Missing Status:
  • Select the ‘Status’ column
  • Go to ‘Transform’ > ‘Replace Values’
  • Replace null with “Not Recorded”
  1. Sort the Data (Optional):
  • Go to ‘Home’ > ‘Sort’ > ‘Sort Ascending’ on the EmployeeID column
  • Then sort ascending on the Date column

This method fills in missing dates for each employee without creating additional date ranges:

  • The ‘Fill Between’ operation adds any missing dates within each employee’s date range.
  • We then fill in the status for newly added dates as “Not Recorded”.

This approach is efficient because:

  • It works directly with the existing data without creating helper columns or tables.
  • It automatically adjusts to each employee’s specific date range.
  • It preserves the original data while adding only the necessary missing dates.

Troubleshooting:

  • If you see unexpected results, check for any time components in your Date column.
  • Ensure your Date column is correctly typed as a Date or Date/Time in Power Query.
  • If you have multiple date columns, you can apply this method to each column separately.

By following these steps, you’ll have a complete dataset with no missing dates, allowing for accurate time-based HR analytics and reporting.

Additional Keywords: Date Continuity, Power Query, Time Series Completion, HR Data Preparation