Two Tables - How to Subtract data

[Ciria]

Dears Folks!:

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:

1. -First I append two tables together
2. -Second I use LefAntiJoin to remove the lines that match between AppendNewTable and PO’s_amend Table
3. -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…

Kind regards,

[Paul]

Hi

Suggest you read this article about keeping errors and subsequently creating a correct table.

https://radacad.com/exception-reporting-in-power-bi-catch-the-error-rows-in-power-query

Need to see the structure of your data, you can either use append and remove duplicates to keep the latest?(by modification date?)corrected records or
if you merge the tables (by PO) with the corrections table and use conditional columns to identify records/content that you wish to keep you should be ok.

If you can try to import from folder so you can save subsequent months in the folder and automate the whole process.

Hope this helps.

Paul

[sam.mckay]

I always get a bit concerned when seeing fix ups or bandaids like this placed over raw data that isn’t correct.

Whilst Power BI is really powerful in many ways it’s just can’t fix up data that isn’t correct or captured correctly.

The reality in my view is that placing bandaids and work arounds poor data is a recipe for even further issues down the line.

Fixing the raw data is the most recommended option here. Fixing it at the source is just essential in my view.

Chrs
Sam

[jpratt]

Hi @Ciria,

Since it seems that you are loading data into a data model to do reporting on, I would suggest that a straight “truncate and load” paradigm would be best. That means just replace all your data with every load. Then you will always have a correct snapshot of your data.

If, however, you happen to have a relational data warehouse with an actual ETL process, you might be able to use an incremental load into the DW that just picks up the records that changed. This requires some complex pieces through, including at least record edit dates in the source system and an ETL process that tracks the last time it ran so the next time it only grabs records changed after that time.

Even in the above case, you would still use a “truncate and load” in your data model for your Power BI report because the DW has the updated data.

So, if this info isn’t helpful, we would need to know more about what you are trying to do in your data model/report and more about any issues exporting the full updated source data from SAP.

John C. Pratt

[EnterpriseDNA]

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.