Using Dax or Mcode to "unstack" a column combining multiple hierarchy elements

[Guitarmageddon]

Ok folks, this might be a little confusing so Im trying to be concise. I have a user's report output that is a list of let's call them "trouble tickets." Their data is really muddy and has a few columns which pertain to both a)what business area they fall into and 2) what Buyer owns that area. In a perfect world every one of these lines should have both the buyer AND business area aka "department" listed in two other related columns, but not the case. There is a THIRD column, that allows the field users to pick from BOTH of those areas and combine into a third column at their entry page. this area has every row populated. The problem is the user whom enters could pick from either element of the hierarchy and combine into one column. So In my effort to create a complete list of both buyers and departments in another column, I am hitting a wall. The end goal is using this in a dashboard in power BI.

here is an example of the source data which I am loading into Power BI. There is a column M of DEPARTMENT that for various reasons, the report output only populates SOME of the time. Additionally BUYER column Y is only populated SOME of the time. however, TROUBLE BUYER column R is populated in every case. HOWEVER, it is a smattering of hierarchy, chosen between either the department OR the buyer.

What I am driving at is trying to arrive at a column fully populated with all the buyers. I have a hierarchy sheet showing what buyers own what department name.

Perhaps a M code function in power query to say a custom column of "carry over all buyer names from column Y, then, for every blank column, if its a buyer name, move it over, if its a department name, go to the hierarchy and find the associated department name. I tried to use "related" as a dax measure in Power BI after setting a "many to many" relationship between buyer names on my hierarchy and buyer names on the output below, but no luck in getting that to work. Let me know if theres anything else I can provide to help clear this mud up.

 

[Guitarmageddon]

Well, one note I just learned as that you cant use RELATED when a many-to-many relationship is used...so I guess relating the hierarchy table to my data table is out. since the only distinct value on the hierarchy is NOT used on the report output above..... may have to talk to the developers because this juice might not be worth the squeeze.