Chihiro
Excel Ninja
Recently I helped someone with following issue in another forum and thought I'd share the solution.
Data sent to him had merged cells. Signifying same value for more than one row. But for different instance. To add complexity not all columns had values all the time.

So, regular filldown operation wasn't enough. As there was need for logic to stop filldown at each grouping.
Here's steps to produce identical values for Sample 2 & 3 and other merged ranges.
1. Instead of normal "From table" create named range that holds all cells with data. I named it "SampleData"
2. In PQ Editor, use blank query and add following as source.
3. Promote first row as header and add Index column.
4. Add custom column with following formula.
5. Select the added custom column and "FillUp". This creates group index to be used in filtering operation.
6. Add filter step on the custom column. Using Advanced options filter for Custom = 3 and [ITEM #] <> null
7. Select all columns and "FillDown"
8. Right click on Filtered Rows step and "Extract Previous". This effectively splits query in 2 sections. I renamed initial portion as "SampleQuery" and latter part as "myFillDown".
9. Go to query containing Filtered Rows step and go into Advanced Editor. Add following line before "let".
Also replace [Custom] = 3 with...
This converts the query to custom function.
10. Create blank query and use following as source. This will create distinct list of Gouping index.
11. Convert list to table using List Tools. Then add custom column by invoking custom function.

12. Expand all but [Index] and [Custom] columns and remove [Column1].
13. Change data types to appropriate ones and load back to sheet.

Note: I didn't make named range dynamic, but as long as it is dynamic, query will update as new data are added.
Data sent to him had merged cells. Signifying same value for more than one row. But for different instance. To add complexity not all columns had values all the time.

So, regular filldown operation wasn't enough. As there was need for logic to stop filldown at each grouping.
Here's steps to produce identical values for Sample 2 & 3 and other merged ranges.
1. Instead of normal "From table" create named range that holds all cells with data. I named it "SampleData"
2. In PQ Editor, use blank query and add following as source.
Code:
= Excel.CurrentWorkbook(){[Name="SampleData"]}[Content]
3. Promote first row as header and add Index column.
4. Add custom column with following formula.
Code:
= if [#"ITEM #"] = null then [Index] else null
5. Select the added custom column and "FillUp". This creates group index to be used in filtering operation.
6. Add filter step on the custom column. Using Advanced options filter for Custom = 3 and [ITEM #] <> null
7. Select all columns and "FillDown"
8. Right click on Filtered Rows step and "Extract Previous". This effectively splits query in 2 sections. I renamed initial portion as "SampleQuery" and latter part as "myFillDown".
9. Go to query containing Filtered Rows step and go into Advanced Editor. Add following line before "let".
Code:
(GroupNum as number) =>
Code:
[Custom] = GroupNum
10. Create blank query and use following as source. This will create distinct list of Gouping index.
Code:
= List.Distinct(SampleQuery[Custom])
11. Convert list to table using List Tools. Then add custom column by invoking custom function.

12. Expand all but [Index] and [Custom] columns and remove [Column1].
13. Change data types to appropriate ones and load back to sheet.

Note: I didn't make named range dynamic, but as long as it is dynamic, query will update as new data are added.