Refer to previous row within same column

[speedytimmes]

Hi there,

i have the below table, where supplier name is buried within invoice no. column.

I want the supplier names in a separate column and in Excel I know how to do it (see table column C & D).

How do I get to the exact same result (column D) with Power Query?

I have tried multiple things, but nothing what worked so far.

Many thanks in advance.

Best regards

Timo

	A	B	C	D
1	Invoice No	Description	Excel Formula	Result
2	null	null
3	Porter Supplies	null	=if(and(A3<>0;B3=0);A3;C2)	Porter Supplies
4	INV_801	xyz	=if(and(A4<>0;B4=0);A4;C3)	Porter Supplies
5	INV_802	xyz	=if(and(A5<>0;B5=0);A5;43)	Porter Supplies
6	Porter Supplies	null	...	Porter Supplies
7	null	null	...	Porter Supplies
8	Smith Supplies	null	...	Smith Supplies
9	INV_2023	xyz	...	Smith Supplies
10	INV_2024	xyz	...	Smith Supplies
11	Smith Supplies	null	...	Smith Supplies
12	null	null	...	Smith Supplies
13	...	...	...	...

[Jakinta]

If the supplier names will always in the row where the pattern is [#"Invoice No "]<>null and [Description]=null, then this might help.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcorzcmBUbE60UpGQE5AflFJapFCcGlBQU5majGytDGQ4+kXFm9hANJcUVkFFjWBixohiZpiNQosZYbFZnMgJzg3syQDq8UWUCuMDIyMkeywRAibIAkbGuA1zBDD57EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, #"Invoice No " = _t, Description = _t]),
    ToRecreateOriginalTableNulls = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{" ", "Invoice No ", "Description"}),
    Custom = Table.AddColumn(ToRecreateOriginalTableNulls, "Result", each if [#"Invoice No "]<>null and [Description]=null
then [#"Invoice No "] else null),
    #"Filled Down" = Table.FillDown(Custom,{"Result"})
in
    #"Filled Down"