Dealing with vertically merged cells - Sample how to

[Chihiro]

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.

= Excel.CurrentWorkbook(){[Name="SampleData"]}[Content]

3. Promote first row as header and add Index column.

4. Add custom column with following formula.

= 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".

(GroupNum as number) =>

Also replace [Custom] = 3 with...

[Custom] = GroupNum

This converts the query to custom function.

10. Create blank query and use following as source. This will create distinct list of Gouping index.

= 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.

 

[NARAYANK991]

Hi ,

Would a VBA solution have taken longer to code and test ?

Would it be reliable and robust ?

Narayan

 

[Chihiro]

Above is robust and reliable. VBA is definitely an option.

It will be mostly up to personal preference which route you'd take and version of Excel you have access to.

You can make it bit more flexible by adding dynamic column name to custom function. Replace column names with Table.ColumnNames(Source).

I tend to prefer PQ over VBA solution where I can, as majority of steps can be done using UI and so much easier to teach junior member of the process. I've never been able to successfully train someone to manage VBA code solution without my support.

 

[Chihiro]

FYI - Steps looks long. But when you actually do it, it won't take that long at all. Took me 3 minutes to build the sample. Write up took much longer

 

[shrivallabha]

FYI - Steps looks long. But when you actually do it, it won't take that long at all. Took me 3 minutes to build the sample. Write up took much longer

True. Sometimes I have hated writing but documenting things is important from long run perspective.