Translate to M

[Aleksandra_MLT]

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)

[wdx223_Daniel]

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

[v-angzheng-msft]

Hi, @Aleksandra_MLT 

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

[ronrsnfld · Accepted solution]

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

[Aleksandra_MLT]

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]

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

[v-angzheng-msft]

Hi, @Aleksandra_MLT 

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