Forum Discussion
Helper IReassigining columm values from another table /Merge, lookup or relations ?
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
Hi Emrecan,
Have you tried to have the join done in SQL?
i.e. write the SQL, do the join in "WHERE"
This should help avoid calculations done in Power BI.
Regards
KT
3 Replies
- KT_Bsmart2gethe
Impactful Individual
Hi Emrecan,
Have you tried to have the join done in SQL?
i.e. write the SQL, do the join in "WHERE"
This should help avoid calculations done in Power BI.
Regards
KT
EmrecanHello KT_Bsmart2gethe
sadly the salesforce table is currently unavailable on oracleDB and it is obtained using powerquerry connection as such we cant join the tables in SQL.Another team is currenctly trying to deposit salesforce data in oracle but we have no access to their progress
- KT_Bsmart2gethe
If that's the case, maybe you can add Table.Buffer() to the reference table.