Forum Discussion
How do I use nested lookup tables?
Well, I could use M code in PowerQuery, in Excel/Access/PowerBI.
And I suppose I have to ... but what I'm trying to find is a better way. If I was using SQL, I wouldn't have to, but in this PowerQuery world, a simple lookup requires a whole lot of code. I'm skeptical that Microsoft would make it more complex to solve the same problem, using newer software, hence my question.
This is the lookup table
And the results that come through are coming through as "Level 1 again.Level 2. Level 3" which means that I have to parse and decode the string.
The answer is going to be :
- The column that draws the data from the lookup table has been incorrectly constructed, or
- Microsoft's newer software (SharePoint + PowerQuery) is more complex than straightforward SQL - deal with it, or
- There's another new approach, which is .....?????
Hi Netrelemo ,
Sorry, misread your original question. I think you are right that there is no "simple" solution that would not require a fair bit of coding. In fact, the problem only looks simple, because of the way our brain works with patterns. Looking from a machine logic perspective this does require a more or less complex algorithm to define the hierarchy.
Sharepoint does not do it much differently, it just hides implementation details to give a ready-answer, but on the backbone, the seemingly similar nodes have quite unique identifiers, which enable this behaviour. The point is that some code has to be run on one side or another to enable this behaviour. In Sharepoint this happens on server side, in PQ it needs to happen via SQL or M.
Quite logically, dev team has decided not to try to provide a solution/function for every possible case with badly structured data, but instead grant enough flexibility in the language to overcome most issues. 😀
Theoretically, you can write a universal code that would deal with any number of levels in the hierarchy and use this function every time you need to brush this or similar field.
Kind regards,
JB