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_CODEREFERRAL_CONTRACTS_CODEBASE_REFERRAL_CODE
12(null)
22(null)
35(null)
4(null)3
51(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

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"

     

     

     

3 Replies

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Super User
    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"

     

     

     

    • cal_hallum's avatar
      cal_hallum
      Regular Visitor
      2 years ago

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

  • Ahmedx's avatar
    Ahmedx
    Super 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"