Replacing a null value with a value fetched from a different row based off a third column

[cal_hallum]

Hi

I have a table with three columns, REFERRAL_CODE, REFERRAL_CONTRACTS_CODE and BASE_REFERRAL_CODE

REFERRAL_CODE	REFERRAL_CONTRACTS_CODE	BASE_REFERRAL_CODE
1	2	(null)
2	2	(null)
3	5	(null)
4	(null)	3
5	1	(null)

I would like to replace the null value in REFERRAL_CONTRACTS_CODE with the corresponding value from the row where BASE_REFERRAL_CODE = REFERRAL_CODE

For example, in this case the BASE_REFERRAL_CODE = 3, so it would get the REFERRAL_CONTRACTS_CODE = 5 from row 3 and replace the null value with that

[Ahmedx]

Is this what you are looking for?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYqVYnWgwA84xBjJMYRwTEEMHKAbigEQNwTKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [REFERRAL_CODE = _t, REFERRAL_CONTRACTS_CODE = _t, BASE_REFERRAL_CODE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"REFERRAL_CODE", Int64.Type}, {"REFERRAL_CONTRACTS_CODE", Int64.Type}, {"BASE_REFERRAL_CODE", Int64.Type}}),
    f = (x)=>
[
t =  List.PositionOf(x[BASE_REFERRAL_CODE], List.Select(x[BASE_REFERRAL_CODE],  (z)=> z <> null){0})-1,
 to = x[REFERRAL_CONTRACTS_CODE]{t}][to],
    from = #"Changed Type",
    #"Replaced Value" = Table.ReplaceValue(from,each [BASE_REFERRAL_CODE],each if [BASE_REFERRAL_CODE]= null then f(from) else [BASE_REFERRAL_CODE],Replacer.ReplaceValue,{"BASE_REFERRAL_CODE","REFERRAL_CONTRACTS_CODE"})
in
    #"Replaced Value"

[marcelsmaglhaes · Accepted solution]

Hey @cal_hallum 

You can achieve this using Power Query in the Power BI Desktop. Try the script bellow.
If this post helps, please mark as solved.
Regards,
Marcel

Script Power Query

let
// Replace the line below with your current data source
Source = YourCurrentTable,

// Custom function to look up the corresponding REFERRAL_CONTRACTS_CODE value
CustomFunction = (referralCode) =>
let
correspondingRow = Table.SelectRows(Source, each [REFERRAL_CODE] = referralCode),
result = if Table.IsEmpty(correspondingRow) then null else correspondingRow{0}[REFERRAL_CONTRACTS_CODE]
in
result,

// Add a custom column with the desired values
#"Added Custom" = Table.AddColumn(Source, "New_REFERRAL_CONTRACTS_CODE", each CustomFunction([BASE_REFERRAL_CODE]))
in
#"Added Custom"

[cal_hallum]

This has worked great thanks, I've added another step replacing the null values with the New_REFERRAL_CONTRACTS_CODE value to complete this