Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dataguy254
New Member

Returning original values in Power Query

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.

 

IDappIDjobIDfrom
1400900213
1401213100
1402100500
1403500617
1404617 
278176496
278296528
2783528 

 

 

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

 

IDappIDjobIDfromoriginal
1400900213617
1401213100617
1402100500617
1403500617617
1404617  
278176496528
278296528528
2783528 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!

2 REPLIES 2
slorin
Super User
Super User

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
Super User
Super User

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors