LOOKUPVALUE from same table?

[macfuller]

I want to get a parent value into a new column based on a hierarchy in the same table, but the DAX documentation says LOOKUPVALUE doesn't work from the same table. I have a crude workaround but I'm thinking there must be something simpler.

We have a single table of Global Location Numbers (GLNs), the GS1 location standard. (Simplified version below) A ship-to location corresponds to a street address, a deliver-to to a location within that street address. Every ship-to can have multiple deliver-tos. Our orders are delivered to the deliver-to locations (duh) but we'd like to group deliveries by the street addresses.

The file has a Parent GLN column that has the GLN for the related ship-to address. We could group by just using parent GLN in the pivot table but it's a 13-digit number and pretty meaningless. So we'd like to do it by the location name of the parent GLN.

Sounds like a simple job for a new column ParentName using LOOKUPVALUE but that gives errors, probably for the reason given above. I have the formula below that works but it seems awfully complicated, so I'm wondering if there's a simpler option I've missed.

=
IF (
GLN[Location Type] = "Ship To",
GLN[Descr],
CALCULATE (
FIRSTNONBLANK ( GLN[Descr], 1 ),
FILTER ( GLN, GLN[GLN#] = EARLIER ( GLN[Parent GLN] ) )
)
)

Even better would be a way to do this in PowerQuery, but the only ways I've seen involve merging queries from the same table so a calculated column seems easier.

GLN#	Descr	Location Type	Parent GLN	*Parent Name
1	Hospital A	Ship-To
2	Emergency	Deliver-To	1	Hospital A
3	OR	Deliver-To	1	Hospital A
...
10	Hospital B	Ship-To
11	Emergency	Deliver-To	10	Hospital B
...

<tbody>
</tbody>

 

[MarcelBeug]

This would be a Power Query solution. I added a temporary index to restore the original sort order at the end.

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Parent GLN"},#"Added Index",{"GLN#"},"Parent",JoinKind.LeftOuter),
    #"Expanded Parent" = Table.ExpandTableColumn(#"Merged Queries", "Parent", {"Descr"}, {"Parent Name"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Parent",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

 

[macfuller]

Thanks, that's doing what I wanted. But my SQL isn't as good as it could be... the Ship-To locations are getting a blank value (because there's no parent for them) but I'd like to duplicate location description if it's a Ship-To. I could add a custom column with an IF statement after the merge but wondered if there's a better way within the merge process itself?