Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi All,
I need to get count of columns (where value is not null or empty) in new custom column. I searched a lot but couldnt find solution. can anyone help me witht thi.
Sample dataset and expected output in ColumnCount
Id Column1 Column1 Column1 Column1 ColumnCount
=============================================================
1 textvalue textvalue textvalue textvalue 4
2 textvalue textvalue 2
3 textvalue 1
Thanks in advance
Solved! Go to Solution.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUSpJrSgpS8wpTSWKHasTrZSERyUIgdQkQ9gYUrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Id", "Column1", "Column2", "Column3", "Column4"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "ColumnCount", each List.NonNullCount(List.RemoveFirstN(Record.ToList(_),1)))
in
#"Added Custom"
👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly -- How to provide sample data
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUSpJrSgpS8wpTSWKHasTrZSERyUIgdQkQ9gYUrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Id", "Column1", "Column2", "Column3", "Column4"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "ColumnCount", each List.NonNullCount(List.RemoveFirstN(Record.ToList(_),1)))
in
#"Added Custom"
👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly -- How to provide sample data
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
6 | |
6 | |
6 |