how to repeat column value in PowerQuery

[amalrio]

Hi Gurus, 

I have two columns in PoweBI, I want to repeate category name and subcategoty name until  there is a new value, 

I want to know how to get it done in Power Query.

my current tabel and expected values are showen in the image below. 

Thanks a lot for your help in advance

[amitchandak]

@amalrio , There can be few ways.One can be.

Make all Test as null . In power Query -> Right click on Column and Replace "Test" with:  null

Then use Fill Down - > https://www.excelcampus.com/powerquery/fill-down-blank-null-cells-power-query/

Replace remaining null with Test

[AllisonKennedy]

@amalrio  This is one of my favourite Power Query functions. 

UPDATE: I forgot to mention that it only works on null values, then picks up next non null value, so first you must: 

Select the column,

Click Transform tab, 

click 'Replace Values'

Replace 'Test' with 'null' (spelling must be exact match).

Then click Fill > Down.

[amalrio]

Hi @AllisonKennedy ,

thanks for the reply, but it did do anythin to to get me the expected table as in the image.

[AllisonKennedy · Accepted solution]

Ah, sorry, I didn't look carefully enough at your image, got too excited about fill down! lol

This function only works on 'null' values, so you'll need to select the column, click 'Replace Values'

Then use fill down.

[amitchandak]

@amalrio ,

There can be few ways.One can be.

Make all Test as null . In power Query -> Right click on Column and Replace "Test" with:  null

Then use Fill Down - > https://www.excelcampus.com/powerquery/fill-down-blank-null-cells-power-query/

Replace remaining null with Test