Stuck trying to fill cells in PowerQuery

[Anonymous]

I am fairly new to PowerQuery and still learning.   I have a query that is very close to what I need (this file is made smaller for clarity withy rows and columns removed)

For Members with a single line entry, there is no problem as this is what I need - e.g lines 266 and 647 and others lower down

The problem is for members with multiple roles where they do training course when in a specific role - e.g. Member 811 (Helen) who has eight separate roles.   I cannot seem to work out how to do the following

• For Courses 1 to 4 (and other columns like them) where there is a single date entry in all the roles (rows), or multiple identical date entries, I need to fill the other blank cells in that column with the same date already in that column.
• For Courses 5 and 6, I need to populate any blanks with the most recent date, leaving the others as they are

I'm sure it must be 'obvious' to experienced users, but I am stuck......and very frustrated.

I do hope someone can help - thanks in advance !!

[v-juanli-msft]

Hi @Anonymous 

Please refer to:

https://radacad.com/grouping-in-power-query-getting-the-last-item-in-each-group

https://www.excelcampus.com/library/fill-down-blank-null-cells-power-query/

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks !!

Only just seen the response - sorry for the delay !

[edhans]

@Anonymous Please mark one or both as the solution that helped so this thread can be closed, or post back if you still have questions.

[MattAllington]

this data structure is not the best. Have you considered unpivoting the columns?  Halfway tthrough this article I show you how. https://exceleratorbi.com.au/understanding-power-query-combine/