Forum Discussion

speedytimmes's avatar
speedytimmes
New Member
3 years ago

Refer to previous row within same column

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

 

 ABCD
1Invoice No DescriptionExcel FormulaResult
2nullnull  
3Porter Suppliesnull=if(and(A3<>0;B3=0);A3;C2)Porter Supplies
4INV_801xyz=if(and(A4<>0;B4=0);A4;C3)Porter Supplies
5INV_802xyz=if(and(A5<>0;B5=0);A5;43)Porter Supplies

6

Porter Supplies

null...Porter Supplies
7nullnull...Porter Supplies
8Smith Suppliesnull...Smith Supplies
9INV_2023xyz...Smith Supplies
10INV_2024xyz...Smith Supplies
11Smith Suppliesnull...Smith Supplies
12nullnull...Smith Supplies
13............
Need Help
Tips and Tricks

1 Reply

  • Jakinta's avatar
    Jakinta
    Solution Sage
    3 years ago

    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"