Returning original values in Power Query

[dataguy254]

Hello all,

I recently ran into an issue that I was able to fix using some DAX language, but it is causing a lot of issues with load times.

In the below table I have some sample data. The ID field is the ID for the specific person, the appID is an application they submitted, jobID is the position they applied to, and from is a linking of the jobID. As we can see, ID 1 applied to jobID 900, which is from jobID 213, jobID 213 is from 100, so on and so forth until we reach jobID 617, which is does not have a from value, meaning it is the original.

ID	appID	jobID	from
1	400	900	213
1	401	213	100
1	402	100	500
1	403	500	617
1	404	617
2	781	764	96
2	782	96	528
2	783	528

What I would like to get is a column that gives us the original jobID on each row. Something like the below

ID	appID	jobID	from	original
1	400	900	213	617
1	401	213	100	617
1	402	100	500	617
1	403	500	617	617
1	404	617
2	781	764	96	528
2	782	96	528	528
2	783	528		528

I was able to create this in DAX using multiple LOOKUPVALUE functions and IF statements, but I wanted to know if this could be done easier within Power Query. As the DAX calculated column is causing loading issues. I imagine I would need to create a loop in Power Query using an M language equivalent to LOOKUPVALUE.

Thanks!

[slorin]

Hi,

"the jobID which does not have a from value, meaning it is the original."

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vcy7EcAwCAPQXVRTGHD8mcXn/dcIOORCGhVPJ60FBqGWYjlPCis2vc4hBLb2cwkhXD/XEELjnryGEA76vA+vevNqtsTygB3JSKwhfrFv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, appID = _t, jobID = _t, from = _t]),
No_From_Value = Table.SelectRows(Source, each [from] = "" or [from] = null),
Join = Table.NestedJoin(Source, {"ID"}, No_From_Value, {"ID"}, "No_From_Value", JoinKind.LeftOuter),
Original = Table.ExpandTableColumn(Join, "No_From_Value", {"jobID"}, {"original"})
in
Original

Stéphane

[lbendlin]

Not sure the Power Query version will be any faster. But in DAX you can use the PATH functions instead of LOOKUPVALUE.  Have you tried that?