Creating a separate lookup table, versus using M code if/then within original table....

[Guitarmageddon]

Hey folks. I asked this question a couple weeks ago but think I did a really poor job at explaining it so I wanted to take another stab.

I have a report output that is generated from users going to a form, and selecting from a dropdown what merchandise area OR buyer owns this "trouble item." They create entries throughout the month and then an output of all entries happens at the end of the month.

I have a hierarchy that spells out the relationship of buyers to merchandise hierarchy areas etc. However, that one single dropdown the users can select contains two aspects of the hierarchy. So when I import the data, I have this really clunky column that can contain different levels of the hierarchy. So when It comes to dashboarding this (which is the end goal), the summarization is much harder. This is really messy data.

What I think I can make work to establish the correct relationships, is something like the below, but I need some input on how I can do with power BI/query

here would be a notional hierarchy example of what I have, pretty straightforward nothing out of the ordinary there. Note, the buyer owns multiple departments, I just abbreviated here. However, the department : buyer relationship is the one : many if that helps clarify.

However, the user entry form allows them to pick EITHER dimension of the hierarchy, and mashes it into one column on the output, and with the above notional structure in mind, looks like a random mix of this depending on what they select. It throws it into a single column called "trouble area." There is other data in columns associated, Im just isolating the one column creating my issue.

What I believe cleans up the data in a structure usable for my end lookup/dashboarding summary, would be creating a couple columns in a side table like this below. That way, if the user enters the buyers name, then its the buyers name used. But if they pick the buyers requisite department, then it still uses the buyers name in the "buyer" column. End result, Im able to dashboard this data buy the buyers name as requested.

So I guess the bottom line is, should I be doing this cleanup by making this extra dummy table outside of the original imports, or, is there some way to create a custom column with M code to just add this column onto the original imported data table, negating the need for creating more tables? I start to think of how I could use some sort of "if/then" logic within M code to do this but it seems atypical and my brain explodes.....

 

[Automatrix]

I still don't understand what you are trying to do. Can you give us an example of the desired output?

 

[Guitarmageddon]

I still don't understand what you are trying to do. Can you give us an example of the desired output?

Well, here is one of the tiles on my dashboard. Im summarizing some time data related to each "trouble entry" and the goal is to summarize by buyer. However, when I have a mix of hierarchy levels used in the same column, I cant do that easily. For example, trim is owned by tracey, yet, is on two different lines. Or, joe also owns the hardware area, yet, summarizes on two different lines. This is why I need to try and get some sort of lookup to dynamically do this for me, versus haven't to manually intervene in excel every month. Im hoping this makes more sense?

 

[GraH]

In the hierarchy, duplicate Buyer, select this copied column, Unpivot other columns.
Use this as lookup table.

 

[Automatrix]

Merge the export example query with the hierarchy query (Left Outer) by the Department column and expand Buyer. This will yield "Smith" for the export value "Spoons", but a null value for the export value "McDonald". Then it's just a conditional column - if null then the export value (McDonald), else the merged value (Smith). This converts all departments into buyers, and it's ready for the dashboard.

 

[GraH]

Merge the export example query with the hierarchy query (Left Outer) by the Department column and expand Buyer. This will yield "Smith" for the export value "Spoons", but a null value for the export value "McDonald". Then it's just a conditional column - if null then the export value (McDonald), else the merged value (Smith). This converts all departments into buyers, and it's ready for the dashboard.

What if there is another null value? This sounds less robust then the technique I proposed.

 

[Guitarmageddon]

Massive thanks to both of you @GraH and @Automatrix . Both of the methods you suggested were viable. In the case of the table joins, the way the data comes in it is "impossible" that this method would not work so its an easy path to my end result. However, I also didn't even think of the "unpivot other columns" method. Let me tell you how much I fumbled around trying to arrive at the result this method gave me, so obvious yet it illuded me. Big thanks for bringing that solution up.

However, using the table lookup method I did have some confusion on how to set the relationship from the lookup to the data table. Even after no duplicates in the hierarchy/lookup table, it still does not let me set a one to many relationship. Im assuming many to many still will behave correctly though if I set the filter relationship correctly. Im just not too experienced with that currently.