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 checke...
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

     

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

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      4 years ago

      Here is a much faster method for the formula for the Custom Column (no Index column required):

       

      List.Count(List.RemoveNulls(
             List.Transform(
               {[Name],[Description],[Alias],[Project Required]}, 
               each if _ = "" then null else _)))
               /4

       

      Took a fraction of a second for 100,000 rows