cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fess440
Helper III
Helper III

add countblank to table.profile

Hi,

 

I use table.profile to get the data profile of a large dataset with many columns. I believe NullCount does not count Blank cells. How can i add CountBlank to this profile? Thanks.

 

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

Try this @fess440 

I don't think you can have blanks for numerical fields - they get converted to nulls, and you cannot replace values with blanks, so I am assuming this is text. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYhOlWJ1opSQUXjKUB+akgBhQNoRpCGanQRUBebEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data 1" = _t, #"Data 2" = _t, #"Data 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data 2", type number}, {"Data 3", type number}}),
    Custom1 = 
        Table.Profile(
            #"Changed Type",
                {
                    {
                        "Blanks", each Type.Is(_, type nullable any),  each List.Count(List.Select(_, each _ = ""))
                    }
                }
        )
in
    Custom1

 

This is my dummy data:

edhans_0-1623185139523.png

And this is the result. Note: I removed a bunch of columns Table.Profile generates to get this screenshot:

edhans_1-1623185204503.png

 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
fess440
Helper III
Helper III

@edhansfantastic. thank you!

Great @fess440 - glad I was able to help. I learned something new here too!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User III
Super User III

Try this @fess440 

I don't think you can have blanks for numerical fields - they get converted to nulls, and you cannot replace values with blanks, so I am assuming this is text. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYhOlWJ1opSQUXjKUB+akgBhQNoRpCGanQRUBebEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data 1" = _t, #"Data 2" = _t, #"Data 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data 2", type number}, {"Data 3", type number}}),
    Custom1 = 
        Table.Profile(
            #"Changed Type",
                {
                    {
                        "Blanks", each Type.Is(_, type nullable any),  each List.Count(List.Select(_, each _ = ""))
                    }
                }
        )
in
    Custom1

 

This is my dummy data:

edhans_0-1623185139523.png

And this is the result. Note: I removed a bunch of columns Table.Profile generates to get this screenshot:

edhans_1-1623185204503.png

 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

edhans
Super User III
Super User III

@fess440 - did you try this solution?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors