Merge Function Left Outer Expand Function

[jlinn]

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!

[edhans]

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.

[jlinn]

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?