Forum Discussion

cal_hallum's avatar
cal_hallum
Regular Visitor
2 years ago
Solved

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

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 (nul...
Need Help
  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    2 years ago

    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"

     

     

     

Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

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"