Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Translate to M

Hey all. Can you help translating this to M ? This way I check level of data comepletenes so in one step I need to check if the value is not blank and also then divide by the number of columns checked:

 

Level1 = DIVIDE(
(IF ( ISBLANK ( TABLE1[Name] ), 0, 1 )
+ IF ( ISBLANK ( TABLE1[Description] ), 0, 1 )
+ IF ( ISBLANK (TABLE1[Alias] ), 0, 1 )
+ IF ( ISBLANK (TABLE1[Project required] ), 0, 1
) , 4)

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    If I understand your question correctly, you can

    1. Add an Index column to your table, so as to be able to define the row
    2. Using List.Accumulate, add a custom column with this formula (which counts the number of nulls and/or blanks and then computes the percentage of columns that are non-blank):

     

    =1-
List.Accumulate({"Name","Description","Alias","Project required"},
                 0, 
                 (state, current)=>
                     if Record.Field(#"Added Index"{[Index]},current) = "" 
                        or Record.Field(#"Added Index"{[Index]},current) = null 
                        then state+1 else state)
     /4

     

6 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    If I understand your question correctly, you can

    1. Add an Index column to your table, so as to be able to define the row
    2. Using List.Accumulate, add a custom column with this formula (which counts the number of nulls and/or blanks and then computes the percentage of columns that are non-blank):

     

    =1-
List.Accumulate({"Name","Description","Alias","Project required"},
                 0, 
                 (state, current)=>
                     if Record.Field(#"Added Index"{[Index]},current) = "" 
                        or Record.Field(#"Added Index"{[Index]},current) = null 
                        then state+1 else state)
     /4

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you, ronrsnfld , your solution slows down PQ a lot and not sure if I'll implement it but the logic seems to work! 🙂

      • v-angzheng-msft's avatar
        v-angzheng-msft
        Community Support
        4 years ago

        Hi, Anonymous 

         

        Is my answer above also helpful?

        If ronrsnfld 's reply is helpful to you, could you please mark it as Answered? It will help the others in the community find the solution easily if they face the same problem with you. 

         

         

        Best Regards,
        Community Support Team _ Zeon Zheng

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, Anonymous 

     

    If I understand correctly, you want to check if there is a null value in the column.
    You can first replace the blank value with null, and then use the List.NonNullCount

     

    code:

    = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"Name", "Description", "Alias", "Project required"})
    = Table.AddColumn(#"Replaced Value", "Custom", each Value.Divide(List.NonNullCount({[Name],[Description],[Alias],[Project required]}),4))

    Result:

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

    =Table.AddColumn(PreviousStepName,"Test",each List.Sum(List.Transform({"Name","Description","Alias","Project required"},(x)=>Byte.From(Record.FieldOrDefault(_,x,null)<>null)))/4)