Hello PBI community
We have encountered a performance challange while converting one of our Excel reports to Powerbi.
We have two tables sourced from salesforce and Oracle database (Thru a querry).
Certain columms in the table from salesforce need reassingment of values "mainly name changes" but a certain value needs to be replaced accourding to a corresponing value on the table from the oracle DB. The catch here is that the connection that ties the salesforce table and the oracle table is another columm. LEAD ID
Also the relation between Salesforce table and oracle table is many to many.
The resulting reassignment of values would later be used as a selection for the reports user.
We have tried to do merge operations on power querry between the salesforce table and the Oracle DB table but we are having performance issues and freezing on powerbi
The excel solution was to create an if statement for all the name changes and include a Vlookup on one of the instances where the correct name would be retrieved from the oracle table.
SALESFORCE TABLE
| Lead ID | Owner |
| 0e4eeb3a-e8b0-4bdf-a8ac-4514293c8568 | 3-Cold Lead |
| 458231005316946 | 3-Cold Lead |
| 724151801617193 | 3-Cold Lead |
| 1295059457535313 | 4-Sent To Network |
| fb825520-2291-4b14-ab12-f208437016a8 | 4-Sent To Network |
| a5ba7270-4c01-11eb-8b2a-0f06b4a632c9 | 4-Sent To Network |
| f4ddbb9e-29d2-4743-b70b-5ec95643c2e7 | 4-Sent To Network |
ORACLE TABLE
| LEAD ID | DRM |
| 00000992-fe88-4c64-9d82-e3be1b5d50b8 | 1-No Sale Yet |
| 00001520-eca0-11eb-acf9-4d9675ca5a97 | 1-No Sale Yet |
| 00007a50-eda9-11ec-b2bc-8f0dbbed20f5 | 1-No Sale Yet |
| 0000e980-282a-11ec-9ac1-993321cbe4e4 | 1-No Sale Yet |
| 00010d00-dc86-11ec-a29b-bfb77451dfb9 | 2-Sale to Network |
| 0001f9d0-88f4-11ec-9303-27c489c234f8 | 5-Sale Good |
| 0002044a-34cd-4724-a262-fe449db5d98c | 6-Sale Bad |
Name Reassignment table
| Owner(From Salesforce Table) | Status |
| 1.a-To Be Qualified | 3-To Be Qualified |
| 2-On Agent | 3-To Be Qualified |
| 1.b-Dialer To Be Qualified | 3-To Be Qualified |
| 3-Cold Lead | 9-Cold Lead |
| 4-Sent To Network | if "4-Sent To Network" value in salesforce[owner] table, lookup Lead ID of the row in Oracle table and Retrieve DRM from Oracle table |
| 5-Error | X-Error |
| 6-Prelaunch Leads | 3-To Be Qualified |
so basicly it is a name reassingment operation but with a lookup from another table.
there might be a solition for this by using row context transition with iterative functions but ı am not experienced in that area.
Your advice on the best way to proceed would be most welcomed
Thanks
