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

     

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