Fill down on non-blank values using PowerQuery

Question

i'm trying to figure out a way to clean this data set. I'm sure there is a smart way to do it but i can't get my head to it.

I've tried using the Fill Down function but for that i need to find a way to exclude those data values under the date i'd like to keep.

Here is my data

My Data in one column   What i want in two columns

APA  -->   22           null              null
Je 06/01/2022           APA  -->   22     Je 06/01/2022
Ve 07/01/2022           APA  -->   22     Ve 07/01/2022
Lu 07/02/2022           APA  -->   22     Lu 07/02/2022
Ma 08/02/2022           APA  -->   22     Ma 08/02/2022
null                    null              null
AR  -->   6             null              null
Ma 04/01/2022           AR  -->   6       Ma 04/01/2022
Ve 21/01/2022           AR  -->   6       Ve 21/01/2022
Sa 22/01/2022           AR  -->   6       Sa 22/01/2022
Me 23/02/2022           AR  -->   6       Me 23/02/2022
Lu 21/03/2022           AR  -->   6       Lu 21/03/2022
Ma 22/03/2022           AR  -->   6       Ma 22/03/2022
null                    null              null
AS  -->   545           null              null
Sa 01/01/2022           AS  -->   545     Sa 01/01/2022
Sa 01/01/2022           AS  -->   545     Sa 01/01/2022
Sa 01/01/2022           AS  -->   545     Sa 01/01/2022
Di 02/01/2022           AS  -->   545     Di 02/01/2022

Any help appreciated !

Answer 1

Try this

add column, custom column with formula

= if Text.Contains([Column1],"->") then [Column1] else null

right click and fill down this new column

add column, custom column, with formula

= if not Text.Contains([Column1],"->") then [Column1] else null

create a filter on first column

each [Column1]=null or not Text.Contains([Column1], "->"

On the two new columns, transform, replace values .. replace errors ... and enter nothing to get a null

right click and remove original column

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"->") then [Column1] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if not Text.Contains([Column1],"->") then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column1]=null or not Text.Contains([Column1], "->")),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Filtered Rows", {{"Custom", ""}, {"Custom.1", ""}}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Column1"})
in #"Removed Columns"