Parent missing

Hi MattAllington I just happend to see your name when I was browsing the list of questions to find the one that one of our customers has posted and got curious. HenrB I don't know if this is what you need but this calculated column should find the parent code of a given line in the table you use as an example.

=
CALCULATE (
    LASTNONBLANK ( VALUES ( 'table'[code] ), 1 ),
    FILTER (
        ALL ( 'table'[code], 'table'[indentation] ),
        'table'[code] < VALUE ( EARLIER ( 'table'[code] ) )
            && 'table'[indentation]
                = VALUE ( EARLIER ( 'table'[indentation] ) ) - 1
    ),
    ALL ( 'table' )
)

I take it that the 'code' field is a string field hence the 'VALUE()' function. Is it a NAV table I think I can recognize the structure?

Merry Christmas to both of you MattAllington, HenrB

Hi Oxenskiold,

that's a very nice code!

Just in case there is the need to do this in the query editor, the corresponding M-code would look like this:

Table.AddColumn(PreviousStep, "Parent", 
	(row) => List.Max(
		Table.SelectRows(PreviousStep, 
		each  [Code]< row[Code])[Code] 
		and [Indentation]< row[Indentation] 
   			)
      	    )

This is a Custom Column with some hand-edited code (in bold) for the nested "row-context".

It would enable you to break down your PC-hierarchy in an environment where you don't have to define the number of levels in advance and you could also use this simplified method for working with hierarchical schemas in PowerBI:

http://www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-pivot-dax/