Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jlinn
Frequent Visitor

Merge Function Left Outer Expand Function

I am re-creating a salesforce model in PowerBI and have a question in regards to joining the OPPORTUNITY object with the USER object.  What is the best way to convert all of the user ID column values from the opportunity object to their actual name (0QL0g000000CTEWGA4 to Bob Smith)? There are roughly 50 individual columns (executive, manager, owner, ect) that I will need to convert this ID to their true name. I've used the Merge function (Left Outer Join) and the Expanded function to bring in the true name, but will i need to do this for all 50 columns? There seems like a better way to do this than continually merging and expanding each column.  Let me know if there is a better way of doing this!

2 REPLIES 2
edhans
Super User
Super User

I don't know anything about the SalesForce models, but you could try this:

  1. Select the 50 or so columns you are referring to.
  2. Right-Click and Unpivot Columns.
  3. Now those will all be in two columns, the Atribute column which is the old column name, and a Value column, which is the data.
  4. Now do your merge.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
jlinn
Frequent Visitor

Hey thanks for your help!  I was able to unpivot all of these columns, merge the name from the USER object, and expand the name.  The only problem with this is now I need to pivot each of these columns back out so each field is a separate column.  The other problem is my fact (SALES) is now repeated rows, so i will need to remove duplicate rows somehow. Any ideas?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.