Fill down by group

[thomma]

Hi all,

I'm struggling filling down my table in Power Query, see image attatched below for the current structure. I'd like to add a row for every date until the next rows comes up and some of the data changes. Note, this should happen grouped on the 'tryOutQuoteNo' column. 

Hope someone can help me out, thanks in advance!

[Anonymous]

Hi @thomma ,
Thank you for the prompt response @HarishKM  and @rajendraongole1 !

I just wanted to kindly follow up to see if you had a chance to review the previous response provided by Community Memberes. I hope it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.

Thank you.

[HarishKM · Accepted solution]

@thomma Hey,
you can try below m query and it is working as expected

let
// Sample data load
Source = YourDataSource,

// Sort by tryOutQuoteNo and Date
SortedData = Table.Sort(Source,{{"tryOutQuoteNo", Order.Ascending}, {"Date", Order.Ascending}}),

// Group by tryOutQuoteNo
GroupedData = Table.Group(SortedData, "tryOutQuoteNo", {"All Data", each _}),

// Generate a list of dates between the min and max for each group
AddDateList = Table.AddColumn(GroupedData, "Date List", each List.Dates(List.Min([All Data][Date]), Duration.Days(List.Max([All Data][Date]) - List.Min([All Data][Date])) + 1, #duration(1,0,0,0))),

// Expand the Date List to get a row for each date
ExpandedDateList = Table.ExpandListColumn(AddDateList, "Date List"),

// Merge the date list with the original data to fill in missing rows
MergedData = Table.NestedJoin(ExpandedDateList, {"tryOutQuoteNo", "Date"}, SortedData, {"tryOutQuoteNo", "Date"}, "Original Data", JoinKind.LeftOuter),

// Expand the merged columns
ExpandedMergedData = Table.ExpandTableColumn(MergedData, "Original Data", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}), // Add all relevant columns

// Fill Down Missing Data
FilledDownData = Table.FillDown(ExpandedMergedData, {"Column1", "Column2", "Column3"})
in
FilledDownData

In my above logic. I am following below steps

step 1: Group the data by tryOutQuoteNo

Step 2: Sort by Date

Step 3:  Generate a list of all dates within the date range for each group.

Step 4: Merge the generated dates with the original data.

Step 5 :Fill down the missing data within each group
Step 6 : Expand the columns and finalize the table.

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[rajendraongole1]

Hi @thomma  - you want to fill down rows with missing dates (i.e., create a row for each day between existing dates), grouped by tryOutQuoteNo, and carry down the relevant columns (like offeredAmount, status, etc.) until the next record comes up.

please check the attached pbix file for reference.