How do I use nested lookup tables?

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