How do I use nested lookup tables?

[Netrelemo]

SharePoint appears to have this feature where a lookup table is not a flat file, but rather a hierarchy of nested levels. So instead of having a traditional flat structure where every additional attribute is another column, it's managed with an excel like indenting. 

Something like this:

- Division A

-- Sub division 1

-- Sub division 2

Division B

-- Sub division 1

-- Sub division 2

My question is how do I use it? 

Lookup tables are not necessarily directly accessible, and the data comes through as a single column containing "Division B. Sub division 1". It's each sub level appended to the previous parent level, separated by a period. 

So when it comes down to building the presentation view of whatever form, I have to implement various string handling formulas to decode these column. If I want the division only, or the subdivision only... a whole heap of string slicing and dicing has to be done.

I'm hoping that there is a better way? 

[Anonymous]

Hi @Netrelemo,

Quite interesting scenario. There may be a more elegant solution, but I don't have PQ editor at the moment.

I think you can start with a brutal force approach. Assuming that you only have 2 levels the idea could look like:

1. Add an index column [Index]
2. Depending on the size of your data you may want to consider one or more from the following:
2.1. Select only [Division Name] and [Index column] - this will make the table lighter
2.2. Add keys on them - this will help with filtering later
2.3. Buffer the table before proceeding to the next step - this may make it faster, but may not, so test on real data.

3. Add a custom column - the main idea here is - if [Division Name] starts with "Division" or somehow else satisfy the criteria for Level 1 take null, otherwise find a [Division Name] that satisfies the criteria for L1 with the max [Index] that is still less than the current [Index]. Something like this:
Table.AddColumn(PrevoiusStep, "CombinedDivisionName", each if Text.StartsWith([Division Name], "Division") then null else fDivName (PrevoiusStep, [Index]) & [Division Name], type text),

fDivName = (pTable as table, pCurrentIndex as number)=>
let
Filter = Table.SelectRows(pTable, each Text.StartsWith([Division Name], "Division") && [Index] < pCurrentIndex),
Output = List.Last(Filter[Division Name])
in Output

(sorry for typos, hopefully the idea is still clear)

Now you have a column with names that look like: Division A -- Subdivision 1, etc.

Filter out bulls and split the column to separete Divisions and Subdivisions.

This is the simplest I can think of without testing in the editor.

Hope this helps,
JB

[Netrelemo]

Thanks, but I think you're missing the point of my question. That approach is exactly how I'm doing it now (in a slightly different form). So every time I come back to use this data source I have to do all that which you have detailed.

[dax]

Hi Netrelemo,

It seems that you want to transform data structure from sharepoint in M code, right? If so, could you please inform me your current data structure or sample in Edit Queries and your expected output? Then I will try to reproduce this in my environment.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Netrelemo]

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 .....?????

[Anonymous]

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