PowerQuery - Remove null and shift values upwards

Hi All

Below in blue is the existing structure I have (after doing certain transformations). I would like to move the values 'upwards' if the above rows are null values (please refer to the table on the right).

Photo.png

I have seen a similar solution on this forum but the number of rows isn't fixed here - one country can have 10 rows, another only 5 rows. There is a similar solution on Stackflow but I am not sure where to input/how to use it on PowerQuery within PBI.

Many thanks!

If the following presentation works for you, then unpivot your data in PQ, close and load to Power Pivot. Build a measure with ConcatenateX to combine the sports. Tried to sort medals to Gold, Silver, Bronze but could not figure that out.

=CONCATENATEX(Table1,Table1[Value],", ")

Excel 2016 (Windows) 32 bit

	G	H	I	J
10
11	Sport	Attribute
12	Country	Bronze	Gold	Silver
13	Australia	Badminton	Swimming
14	China	Swimming	Rifle, Table Tennis	Archery, Table Tennis
15	USA		Basketball, Swimming, 100m	Archery

Sheet: Sheet1

Please try

Please Login or Register  to view this content.

This is not the same as yours. In the StackOverflow example there is NO category column, so you'd end up with disciplines lined up against the wrong country. There may be a way to adapt it, however I looked at it and wasn't able to work out how to for your scenario.

EDIT: I was hoping that Bo_Ry would come along!!! Nice one!