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

     

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! 🙂

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