Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to Solution.
If I understand your question correctly, you can
=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
=Table.AddColumn(PreviousStepName,"Test",each List.Sum(List.Transform({"Name","Description","Alias","Project required"},(x)=>Byte.From(Record.FieldOrDefault(_,x,null)<>null)))/4)
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
If I understand your question correctly, you can
=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
Thank you, @ronrsnfld , your solution slows down PQ a lot and not sure if I'll implement it but the logic seems to work! 🙂
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
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