The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
19 | |
17 | |
13 |