Power query IF above row same below row formula

[Tivakaran]

Hi,
I need someone to teach me how to copy previous above or below in Power Query.

OK, I did the vendor name by FILL DOWN function in power query. Problem is GNIX is shown below because that row was blank. I need a way to replicate IF(A2=A3,B2,B3) in power query.

first line is blank so it replaced with GNIX from above....supposely it filled with POOJA because as you see the DOCUMENT no is same..... so I need the blank to be filled with same data with same document number....anyone?

 

[JGordon11]

From blue Table1 this M code creates the green table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddConditional = Table.AddColumn(AddIndex, "Custom", each
        let i = _[Index], d = AddIndex[Doc], v = AddIndex[Vendor] in
        try if d{i} = d{i+1} then v{i+1} else v{i} otherwise v{i}),
    RemoveColumns = Table.RemoveColumns(AddConditional,{"Vendor", "Index"}),
    Result = Table.RenameColumns(RemoveColumns,{{"Custom", "Vendor"}})
in
    Result

Book1
	A	B	C	D	E	F
1	Doc	Vendor		Doc	Vendor
2	1	A		1	B
3	1	B		1	B
4	1	B		1	B
5	1	B		1	B
6	2	B		2	C
7	2	C		2	C
8	2	C		2	C
9	2	C		2	C
10	3	C		3	D
11	3	D		3	D
12	3	D		3	D
13	3	D		3	D
14
Sheet1

 

[Tivakaran]

From blue Table1 this M code creates the green table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddConditional = Table.AddColumn(AddIndex, "Custom", each
        let i = _[Index], d = AddIndex[Doc], v = AddIndex[Vendor] in
        try if d{i} = d{i+1} then v{i+1} else v{i} otherwise v{i}),
    RemoveColumns = Table.RemoveColumns(AddConditional,{"Vendor", "Index"}),
    Result = Table.RenameColumns(RemoveColumns,{{"Custom", "Vendor"}})
in
    Result

Book1
	A	B	C	D	E	F
1	Doc	Vendor		Doc	Vendor
2	1	A		1	B
3	1	B		1	B
4	1	B		1	B
5	1	B		1	B
6	2	B		2	C
7	2	C		2	C
8	2	C		2	C
9	2	C		2	C
10	3	C		3	D
11	3	D		3	D
12	3	D		3	D
13	3	D		3	D
14
Sheet1

Hi.

I tried this but my excel gives error message AddIndex as unknown function.

 

[JGordon11]

If you post your code, I'll take a look.

 

[Tivakaran]

If you post your code, I'll take a look.

let
Source = Table.NestedJoin(#"Purchase Lines", {"Document No."}, #"Purchase Orders", {"No."}, "Purchase Orders", JoinKind.LeftOuter),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Unit of Measure Code", "UoM"}}),
#"Expanded Purchase Orders" = Table.ExpandTableColumn(#"Renamed Columns", "Purchase Orders", {"Status", "^Last Modified DateTime", "^GRN Approval Status"}, {"Purchase Orders.Status", "Purchase Orders.^Last Modified DateTime", "Purchase Orders.^GRN Approval Status"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Purchase Orders", each ([#"Purchase Orders.^GRN Approval Status"] = "Open")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Purchase Orders.Status", "Purchase Orders.^GRN Approval Status"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Purchase Orders.^Last Modified DateTime", "Order Date", "Document No.", "^Vendor Name", "No.", "Description", "UoM", "Outstanding Quantity", "Outstanding Amount"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Order Date", "Project Code"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Purchase Orders.^Last Modified DateTime", "Modified Date"}}),
#"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns1",{{"Quantity", "PO Quantity"}, {"No.", "Item No."}, {"^Vendor Name", "Vendor Name"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns2","DELIVERY","LEADTIME",Replacer.ReplaceText,{"Description"})
in
#"Replaced Value"

 

[Tivakaran]

I tried to upload the file but its protected by domain and opening without source file just gives error...

 

[JGordon11]

try

let
    Source = Table.NestedJoin(#"Purchase Lines", {"Document No."}, #"Purchase Orders", {"No."}, "Purchase Orders", JoinKind.LeftOuter),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Unit of Measure Code", "UoM"}}),
    #"Expanded Purchase Orders" = Table.ExpandTableColumn(#"Renamed Columns", "Purchase Orders", {"Status", "^Last Modified DateTime", "^GRN Approval Status"}, {"Purchase Orders.Status", "Purchase Orders.^Last Modified DateTime", "Purchase Orders.^GRN Approval Status"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Purchase Orders", each ([#"Purchase Orders.^GRN Approval Status"] = "Open")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Purchase Orders.Status", "Purchase Orders.^GRN Approval Status"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Purchase Orders.^Last Modified DateTime", "Order Date", "Document No.", "^Vendor Name", "No.", "Description", "UoM", "Outstanding Quantity", "Outstanding Amount"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Order Date", "Project Code"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Purchase Orders.^Last Modified DateTime", "Modified Date"}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns1",{{"Quantity", "PO Quantity"}, {"No.", "Item No."}, {"^Vendor Name", "Vendor Name"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns2","DELIVERY","LEADTIME",Replacer.ReplaceText,{"Description"}),
    AddIndex = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
    AddConditional = Table.AddColumn(AddIndex, "Custom", each
        let i = _[Index], d = AddIndex[#"Document No."], v = AddIndex[Vendor Name] in
        try if d{i} = d{i+1} then v{i+1} else v{i} otherwise v{i}),
    RemoveColumns = Table.RemoveColumns(AddConditional,{"Vendor Name", "Index"}),
    Result = Table.RenameColumns(RemoveColumns,{{"Custom", "Vendor Name"}})
in 
    Result

You may need to add a Table.ReorderColumns step at the end

 

[Tivakaran]

Hi.
Will try on next wednesday as we've started on Public Holiday in here. Thanks in advance!