These days we are trying to build at my company a BI Model obtaining data from our E.R.P. System SAP R3.
This system is very particular to get data but specially to amend data.
NOTE: PO means Purchase Orders.
PROBLEM -HOW TO AMEND DATA ERRORS: One of the problem I am experiencing when I get data from SAP is I cannot amend some records because PO is closed/archived, Project accounting cannot be changed because the invoice has been posted. Dates are incorrect, because 2018 has been placed instead of 2019… you know human mistakes…
ALTERNATIVES: Once Po is closed, there is no way (even with admins rights to change that data), so as you export your data, you would replace your data all the time (with every update, let’s say monthly basis).
Another alternative I consider was to get data from January, then amend in Excel, and then get data from February (copy just right after January data), instead of getting data all the time from January to the current month, but this doesn’t work, because unfortunately we are doing changes now, in August to PO from January… so no easy thing.
POSSIBLE SOLUTION: My point is, therefore, to have two tables. One table which is updated monthly with all running data from January onwards, and a second table with all the corrections (Tables have same column structure) and then subtract.
Table 1: ALLPO’s (all lines from all PO’s)
Table 2: PO’s_amends (only PO’s lines with corrections.)
Data type: Excel Tables
Relationship type: 1 to 1 (Tables can be connected using fullPO number column, which is a unique value)
ACTION EXPECTED:Table 2 should replace/remove those lines that match in Table 1.
How can we do this on PowerBI?
My approach in POWERPIVOT:
- -First I append two tables together
- -Second I use LefAntiJoin to remove the lines that match between AppendNewTable and PO’s_amend Table
- -Third, I append PO’s_amend Table
PROBLEM: This increases the size of the file 4 times (using queries and connections only, not tables in worksheets).
QUESTIONS: Is there any other way to make this easier?, I tried INTERSECT AND CROSSJOIN function but they don’t work showing an error in PowerBi…