Working with SOP.ITEM and Power BI



I'm trying to build a dashboard in Power BI, I'm using an ODBC link from Sage50 Accounts to access the data.  One wrinkle I'm finding is that in the SOP.ITEM deleted order lines still show in the database, because of this its overcounting the value of orders.

Below is the extract from the grid, item 132 has been removed from the sales order, but it still pulls through. I don't know if it will make a difference but this will be an S1 line.

Parents Reply
  • +1 in reply to Ken Starnes
    verified answer

    Trouble is that when a line item is removed from a sales order it is simply orphaned, but remains in the data and does not have the deleted flag set.The orphaned records only get removed if you run a compress operation. Reasons behind this are lost in the mists of time but it has been like that ever since sales orders functionality was added by the looks of it.