Power Query: replicate Month1 record to Month 2 & 3 for the quarter

To replicate the Headcount values for the other two months of the quarter in Power Query in Power BI, you can use the following steps:

1. Load your initial data into Power Query.

2. Create a custom column that contains the quarter of each date. To do this, go to the "Add Column" tab and select "Custom Column." Use the following formula to extract the quarter:

Date.QuarterOfYear([Date])

Expand your data to include all months within each quarter. To do this, use the following steps:

a. Duplicate your query by right-clicking on it in the Queries pane and selecting "Duplicate."

b. In the duplicated query, filter for the first month of each quarter. You can do this by keeping only rows where the quarter equals 1. This will give you only the first month of each quarter.

c. Rename the duplicated query to something like "QuarterStart."

d. Create a custom column in the "QuarterStart" query that generates a list of dates for the entire quarter using the List.Dates function. For example:

List.Dates([Date], 90, #duration(1, 0, 0, 0))

1. e. Expand the custom column to get a list of dates for the entire quarter.

   f. Merge the "QuarterStart" query back into your original query using a left outer join on the "Date" column.

   g. You should now have a table that includes all the dates for each quarter along with the corresponding "Headcount" values.

2. Remove unnecessary columns and reorder columns as needed.

3. Close and load the modified query back into Power BI.

Your final table will contain all the dates for each quarter with the corresponding "Headcount" values from the first month of that quarter.