Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

NULL assigned unique individual values

Hi,

 

Is it possible to the activity mentioned in the subject instead of making all null with single and similar value?

 

Thanks...tksnota

Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    Hi Anonymous 

     

    I have in the passed replaced NULLs using the INDEX feature.

    In the Query editor | Add Columns | Index from then enter the MAX index you have.

     

    I then said if the Original ID is null use the new id else use the original id.

    Delete the old ID and rename this column.

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    It can be done both ways.

     

    M code.

    if [Entry No] = null and [PO] = 116557 and [Creditor Name] = "Advanced Travel Partners UK Ltd" and [Creditor Code] = 410229 and [transtype] = "B"
then "N"
else [transtype]

     

    DAX.

    Column =
IF (
    'Table'[Entry No] = BLANK ()
        && 'Table'[PO] = 116557
        && 'Table'[Creditor Name] = "Advanced Travel Partners UK Ltd"
        && 'Table'[Creditor Code] = 410229
        && 'Table'[transtype] = "B",
    "N",
    'Table'[transtype]
)

     

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

5 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    Hi Anonymous 

     

    I have in the passed replaced NULLs using the INDEX feature.

    In the Query editor | Add Columns | Index from then enter the MAX index you have.

     

    I then said if the Original ID is null use the new id else use the original id.

    Delete the old ID and rename this column.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      thanks...will the index feature work also for this scenario wherein i have thousand of line items with transtype "B" but I only need to change this couple to lines to "N"

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    If you are trying to convert transtype data with null value of Entry_No and transtype value of “B” to “N”, you can try to create a custom column in power query editor and write the following expression. 

    if [Entry_No] = null and [transtype] = "B"
then "N"
else [transtype]

     

    If this is not the result you are expecting, please let me know more in the form of screenshot or dummy table so that we can better help you to solve the problem.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi,

       

      this is great! I can use the m code for the null values and transtype

       

      My other concern is for specific value. Can it be done where in if entry = null, PO has specific value (116557), Creditor Name has specifi value (Advance Travel) and Creditor Code has specific value (410229) then change transtype from B to N. 

       

      Happy to do it for M Code or Dax?

       

      Thanks...tksnota...

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    It can be done both ways.

     

    M code.

    if [Entry No] = null and [PO] = 116557 and [Creditor Name] = "Advanced Travel Partners UK Ltd" and [Creditor Code] = 410229 and [transtype] = "B"
then "N"
else [transtype]

     

    DAX.

    Column =
IF (
    'Table'[Entry No] = BLANK ()
        && 'Table'[PO] = 116557
        && 'Table'[Creditor Name] = "Advanced Travel Partners UK Ltd"
        && 'Table'[Creditor Code] = 410229
        && 'Table'[transtype] = "B",
    "N",
    'Table'[transtype]
)

     

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.