Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mat40220
Regular Visitor

Count the numbers of columns that are not null

Helo developers,

 

I would like create new column 'Result' for have the same output like my exemple.

Count the numbers of columns are not null.

 

Exemple :

Column 1Column 2Column 3RESULT
2nullnull1
null672
nullnullnull0
47103

 

Thanks for your returns ! 

1 ACCEPTED SOLUTION

Hey @Mat40220 , 

You can use the List.NonNullCount how the @AnkitKukreja said.

 

The code is 
#"Added Custom" = Table.AddColumn(YOUR_PREVIEW_STEP, "RESULT", each List.NonNullCount({[Column1],[Column2],[Column3]}))

Best Regards,
Marcel


Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI

If I've helped, don't forget to mark my post as a solution!



View solution in original post

6 REPLIES 6
WayneC
Regular Visitor

Is there any reason why this would return an answer of 1 when all columns counted are null ?

example: (foreman.1.2 is column 8 when referencing the formula below):

 

WayneC_0-1681919119314.png

results:

Screenshot 2023-04-19 114620.png

formula:

"Count_Foreman", each List.NonNullCount(List.Range(Record.FieldValues(_),8,4)))

 

This returns the correct number for all "NonNull" counts, but when all four columns are null, it returns "1" every time.

 

Mat40220
Regular Visitor

Thank you so much @marcelsmaglhaes, your are the boss !

 

Have a nice day !

AnkitKukreja
Super User
Super User

Hi @Mat40220 

 

You can add a custom column in the Power Query editor and use the below formula.

 

List.NonNullCount({[Column 1],[Column 2],[Column 3]})

For Power BI trainings or support dm or reach out to me on LinkedIn.
If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
https://topmate.io/ankit_kukreja
marcelsmaglhaes
Super User
Super User

Hey @Mat40220 ,

Without think too much, I believe you can solve using this measure:

RESULT =
VAR __COUNT1 =  COUNTBLANK('Table'[Column1])
VAR __COUNT2 = COUNTBLANK('Table'[Column2])
VAR __COUNT3 = COUNTBLANK('Table'[Column3])
RETURN
3 - (__COUNT1 + __COUNT2 + __COUNT3)

Try that and tell me if it works.

Best Regards, 
Marcel

Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI

If I've helped, don't forget to mark my post as a solution!



Thank Marcel,

 

But i want write the solution in power query (with M language)

 

Do you have an idea ?

Hey @Mat40220 , 

You can use the List.NonNullCount how the @AnkitKukreja said.

 

The code is 
#"Added Custom" = Table.AddColumn(YOUR_PREVIEW_STEP, "RESULT", each List.NonNullCount({[Column1],[Column2],[Column3]}))

Best Regards,
Marcel


Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI

If I've helped, don't forget to mark my post as a solution!



Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors