CODEWITHSUNDEEP
excelpowerquery
Luna
Luna

Reputation: 45

Excel Power Query: Keep only matched and row above

I wanted to know if Power Query in Excel can handle matching something from another worksheet and keeping only the matching row and the row above it all the while not sorting the list.

enter image description here

Above is the report I get sent daily. It contains orders going out. But we only give our customers their orders if they paid, which our system also catches as an "order". Our database is created that links these two orders together but it does it in a single column with the order in above the order out.

enter image description here

The above is the flat text file from the database that shows the OUT orders and the IN orders (i.e. payments). They are sorted by IN and linked OUT order. The numbers are randomly made by the system.

Can Power Query be used to import this flat text file from the database, match those OUT orders from "Today's OUTS" sheet and the OrdersINs which is always the single row above?

I want to just end up with a sheet that contains Today's OUTS and their linked Order INs.

Thank you.

Upvotes: 1

Views: 932

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Yes, it can.

  • Read in the two tables
  • Add an Index column to the "Links" table to be able to restore original order
  • Do Table.Join with JoinKind.FullOuter (all rows from both)
  • Sort according to the Index column
  • At this point one could either
    • add a custom column to reference the previous row if there is something in the OUTS column or,
    • my preference as it will often be faster: offset the Links column by one; then filter out the nulls

Please read the comments in the code and explore the Applied Steps to better understand the algorithm:

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Outs"]}[Content],
    Outs = Table.TransformColumnTypes(Source,{{"Today's OUTS", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Links"]}[Content],
    Links = Table.TransformColumnTypes(Source2,{{"Order Links", type text}}),

//Add index column to links to restore order after join
    #"Added Index" = Table.AddIndexColumn(Links, "Index", 0, 1, Int64.Type),
    Joined = Table.Join(Outs,"Today's OUTS", #"Added Index", "Order Links", JoinKind.FullOuter),
    #"Sorted Rows" = Table.Sort(Joined,{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),

//offset Links by one row (usually faster than using Index to reference previous row
    prevRow = let
        ShiftedList = {null} &  List.RemoveLastN(Table.Column(#"Removed Columns", "Order Links"),1),
        Custom1 = Table.ToColumns(#"Removed Columns") & {ShiftedList},
        Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(#"Removed Columns") & {"Order IN"})
    in
        Custom2,
    #"Removed Columns1" = Table.RemoveColumns(prevRow,{"Order Links"}),

//Filter out the nulls
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([#"Today's OUTS"] <> null))
in
    #"Filtered Rows"

enter image description here

Edit: Outs without Links will show up in the Outs column with a blank in the In column. Not sure how you might want to handle this

Upvotes: 1

Related Questions