How to calculate missing blanks count in power bi

Nagi_k · May 21, 2021, 7:25am

Hi All,

I am looking for some help, i have a table which has application names (1-20) and each application has 1 to 10 attributes (like fields) .
Now i need to calculate

How many attributes are missing in each application
How many applications are missing attributes - Total count of missing applications
ex: i have 20 applications for 11 of them have all the attributes and 9 are missing
i have to show missing attributes are 9 out of 20 applications

Below is the table for example , look for some help in creating this in power bi

Application Names	Attributes 1	Attributes 2	Attributes 3	Attributes 4	Attributes 5	Missing Attributes
Application 1		Yes		Yes		3
Application 2	Yes	No	Yes	No	Yes	0
Application 3	No		No		No	2
Application 4		Yes		Yes		3
Application 5	Yes	Yes	Yes	Yes	Yes	0
Application 6	No	No	No	No	No	0
Application 7	Yes	Yes	Yes	Yes	Yes	0
Application 8	No	Yes	No	Yes	No	0
Application 9	Yes	No	Yes	No	Yes	0
Application 10	No	Yes	No	Yes	No	0
Application 11	Yes	No	Yes	No	Yes	0
Application 12	No		No		No	2
Application 13		Yes		Yes		3
Application 14	Yes	No	Yes	No	Yes	0
Application 15	No		No		No	2
Application 16		Yes		Yes		3
Application 17		No		No		3
Application 18	Yes	Yes	Yes	Yes	Yes	0
Application 19	No	No	No	No	No	0
Application 20						5

AntrikshSharma · May 21, 2021, 7:43am

@Nagi_k I would suggest you do it in Power Query, paste this code in Power Query editor

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WciwoyMlMTizJzM9TMFTSUQKiyNRiFEasDqoyI7iUXz4mE121MUwSlUZXZkKc3aZwKewkunozmK2YBLpScxKNtsARAFgNtyQpzAwNSDHb0JA0w42IixFDY+KixNCENOtNibTejEjrzTGMwqbKgsTYNbQkPuUYGUCsRqDYWAA=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ #"Application Names" = _t, #"Attributes 1" = _t, #"Attributes 2" = _t, #"Attributes 3" = _t, #"Attributes 4" = _t, #"Attributes 5" = _t ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "Application Names", type text },
            { "Attributes 1", type text },
            { "Attributes 2", type text },
            { "Attributes 3", type text },
            { "Attributes 4", type text },
            { "Attributes 5", type text }
        }
    ),
    MissingAttribute = Table.AddColumn (
        ChangedType,
        "Missing Attribute", 
        each
        let 
            YesNoList = { "Yes", "No" },
            CurrentRow = _,
            ToList = Record.ToList ( CurrentRow ),
            RemoveFirstColumn = List.RemoveFirstN ( ToList, 1 ),
            RemoveYesNo = List.RemoveItems ( RemoveFirstColumn, YesNoList ),
            CountListValues = List.Count ( RemoveYesNo )
        in 
            CountListValues,
        Int64.Type
    )
in
    MissingAttribute

and then in the model you can create 4 measures:

Not Missing Overall = 
CALCULATE ( 
    COUNTROWS ( Nagi ),
    Nagi[Missing Attribute] = 0,
    REMOVEFILTERS ( Nagi )
)

.

Missing Overall = 
CALCULATE ( 
    COUNTROWS ( Nagi ),
    Nagi[Missing Attribute] <> 0,
    REMOVEFILTERS ( Nagi )
)

Not Missing = 
CALCULATE ( 
    COUNTROWS ( Nagi ),
    Nagi[Missing Attribute] = 0
)

.

Missing = 
CALCULATE ( 
    COUNTROWS ( Nagi ),
    Nagi[Missing Attribute] <> 0
)

BrianJ · May 21, 2021, 8:15am

@Nagi_k,

@AntrikshSharma and I were working on this at the same time. He got to the finish line faster, but for the sake of learning and interest, here’s my solution which is a Power Query/DAX hybrid.

I first referenced the original table and then unpivoted all the attribute columns, then joined the original table (which became the Applications dimension table) to the unpivoted data using a 1:M relationship on Application Name.

I then wrote the two DAX measures to count missing attributes and incomplete applications:

Count Missing Attributes = 
CALCULATE(
    COUNTROWS( 'Data Unpivoted' ),
    'Data Unpivoted'[Answer] = BLANK(),
    ALLEXCEPT( Applications, Applications[Application Name]  )
)

Inomplete Application = 
CALCULATE(
    COUNTROWS( Applications ),
    FILTER( Applications, [Count Missing Attributes] > 0  )
)

Here’s what it looks like all put together:

I hope this is helpful. Full solution file attached.

Brian

eDNA Forum - Incomplete Applications Solution.pbix (27.4 KB)

Nagi_k · May 21, 2021, 9:07am

Thanks Brian,

But what if i have applications are around 3000(Count of application names) and column attributes are around 70 to 100.

Will this still work.

MK3010 · May 21, 2021, 9:54am

Hi @Nagi_k

@AntrikshSharma @BrianJ both expert have given answer but I will also add my solution as below.

Create a LookUpTable from the data using below Power Query code:

You can replace the first line with actual data source.

let
    Source = RawData,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Application Names"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.Length([Value]) = 0 then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Application Names"}, {{"Missing Attributes", each List.Sum([Custom]), type nullable number}})
in
    #"Grouped Rows"

Output should be as below.

And Merge this with Actual data. Use below code.

let
    Source = RawData,
    #"Merged Queries" = Table.NestedJoin(Source, {"Application Names"}, LookUpTable, {"Application Names"}, "LookUpTable", JoinKind.LeftOuter),
    ExpandLookUpTable = Table.ExpandTableColumn(#"Merged Queries", "LookUpTable", {"Missing Attributes"}, {"Missing Attributes"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(ExpandLookUpTable, {"Application Names"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Below screen FYR, you can disable the load for Other tables except Data as shown below.

You can then create below measure just to count missing values.

Count of Missing Attributes = 
VAR NonMissingAtt =
    CALCULATE( 
        COUNT('Data'[Application Names]),
        'Data'[Value] = "0"
    )
VAR TotalAttCount = DISTINCTCOUNT( 'Data'[Application Names] )
VAR Result = TotalAttCount - NonMissingAtt
RETURN
    Result

Below screen shot is the output FYR.

Thanks
Mukesh

BrianJ · May 21, 2021, 10:20am

@Nagi_k,

I think it would still run pretty quickly, since the unpivoted fact table is structured long and thin (lots of rows, few columns) which is optimal for DAX.

Brian

Nagi_k · May 21, 2021, 12:31pm

Thanks @AntrikshSharma,

Instead of Yes and No what if i have values like this,

Application Names	Attributes 1	Attributes 2	Attributes 3	Attributes 4	Attributes 5	Missing Attributes
dghewrhwh		ehllng		dfnjrt		3
h4hnD	hannpo	kjbklg	lkblkeg	rjrej	rjer	0
rjre	l;lj;ge		kjngkln		rjrej	2
fdnteajm		Yes		rhj		3
djretj	nnlkdsg	Yes	rhrh	rejre	hrerhhr	0
drjeatj	segwrg	No	rehm	rhreh	hrh	0
rjetj	geegew	Yes	hrreh	rhej	rherb	0
drjer	ghwrh	Yes	er	erjerj	jraetj	0
drjetjn	hwrh	No	hrreh	reje5j	rheetj	0
rehrej	hrrwh	Yes	net	erjej	rhrhj	0
rjerj	hrwrh	No	rahreh	rej5e	rjrea	0
jertj	hw5		rnhreh		rehjr	2
jtejetj		Yes		hrej		3
jrej	rherwh	No	rntteh	r	ghre	0
rjtej	hrhjj		rhreh		rehjr	2
rejerj		reh		rh53y		3
erjrj		rhreh		fdhreh		3
rrw4rjn	erhe5	dherh	rehm	rehy4y	rhj5	0
rejreu	rejherj	rherh	hrea4	rh53y	rahjk	0
reure						5

Appreciate your help here !

AntrikshSharma · May 21, 2021, 12:50pm

@Nagi_k Then just replace the last varible with this:

MissingAttribute = Table.AddColumn (
    ChangedType,
    "Missing Attribute", 
    each
    let 
        CharacterToRemove = { "" },
        CurrentRow = _,
        ToList = Record.ToList ( CurrentRow ),
        RemoveFirstColumn = List.RemoveFirstN ( ToList, 1 ),
        OriginalListCount = List.Count ( RemoveFirstColumn ),
        RemoveCharacters = List.RemoveItems ( RemoveFirstColumn, CharacterToRemove ),
        CountListValues = OriginalListCount - List.Count ( RemoveCharacters )
    in 
        CountListValues,
        Int64.Type
)

MK3010 · May 21, 2021, 1:01pm

HI @Nagi_k

Based on your new data I just want to update Power Query for the LookUpTable as below in which I have removed the hardcoded value of “Yes” and “No” with count of char, it might help you/other users.

let
    Source = RawData,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Application Names"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.Length([Value]) = 0 then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Application Names"}, {{"Missing Attributes", each List.Sum([Custom]), type nullable number}})
in
    #"Grouped Rows"

Also reupdated my previous post.

Nagi_k · May 21, 2021, 2:01pm

Thank @MK3010

Imagine in my table it doesn’t have missing attributes information, then how will i get the output.

As i can see you have used missing attributes in your query

Could you please help me here

Nagi_k · May 21, 2021, 2:13pm

Thanks @AntrikshSharma

One last help instead of Attributes 1, Attributes 2 , i have 64 columns(Like server, network…etc) with different names instead of Attributes 1 …2 …5

In that case how do i proceed like below

Application Names	Server	Network	Storage	APM	Soemthing
dghewrhwh		ehllng		dfnjrt
h4hnD	hannpo	kjbklg	lkblkeg	rjrej	rjer
rjre	l;lj;ge		kjngkln		rjrej
fdnteajm		Yes		rhj
djretj	nnlkdsg	Yes	rhrh	rejre	hrerhhr
drjeatj	segwrg	No	rehm	rhreh	hrh
rjetj	geegew	Yes	hrreh	rhej	rherb
drjer	ghwrh	Yes	er	erjerj	jraetj
drjetjn	hwrh	No	hrreh	reje5j	rheetj
rehrej	hrrwh	Yes	net	erjej	rhrhj
rjerj	hrwrh	No	rahreh	rej5e	rjrea
jertj	hw5		rnhreh		rehjr
jtejetj		Yes		hrej
jrej	rherwh	No	rntteh	r	ghre
rjtej	hrhjj		rhreh		rehjr
rejerj		reh		rh53y
erjrj		rhreh		fdhreh
rrw4rjn	erhe5	dherh	rehm	rehy4y	rhj5
rejreu	rejherj	rherh	hrea4	rh53y	rahjk
reure

AntrikshSharma · May 21, 2021, 2:22pm

@Nagi_k My solution doesn’t depend on the column names, so it will work for this data too.

Nagi_k · May 21, 2021, 2:24pm

Thanks @BrianJ

What if the table looks like this ,

how do i count blanks for each Application
How many applications has blanks (Distinct Count)
Ex: 20 applications , 11 are showing all as the values and 9 applications has blanks

Application Names	Server	Network	Storage	APM	Soemthing
dghewrhwh		ehllng		dfnjrt
h4hnD	hannpo	kjbklg	lkblkeg	rjrej	rjer
rjre	l;lj;ge		kjngkln		rjrej
fdnteajm		Yes		rhj
djretj	nnlkdsg	Yes	rhrh	rejre	hrerhhr
drjeatj	segwrg	No	rehm	rhreh	hrh
rjetj	geegew	Yes	hrreh	rhej	rherb
drjer	ghwrh	Yes	er	erjerj	jraetj
drjetjn	hwrh	No	hrreh	reje5j	rheetj
rehrej	hrrwh	Yes	net	erjej	rhrhj
rjerj	hrwrh	No	rahreh	rej5e	rjrea
jertj	hw5		rnhreh		rehjr
jtejetj		Yes		hrej
jrej	rherwh	No	rntteh	r	ghre
rjtej	hrhjj		rhreh		rehjr
rejerj		reh		rh53y
erjrj		rhreh		fdhreh
rrw4rjn	erhe5	dherh	rehm	rehy4y	rhj5
rejreu	rejherj	rherh	hrea4	rh53y	rahjk
reure

MK3010 · May 21, 2021, 3:26pm

Hi @Nagi_k

If you don’t have any missing attributes then also it will work. The measure “Count of Missing Attributes” will be equal to number of Application. It will work with N number of columns and also don’t not depend on column names.

Thanks
Mukesh

Nagi_k · May 21, 2021, 3:26pm

@AntrikshSharma

I can see in the below Query you have written Attributes 1, attributes 2

let
Source = Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
“i45WciwoyMlMTizJzM9TMFTSUQKiyNRiFEasDqoyI7iUXz4mE121MUwSlUZXZkKc3aZwKewkunozmK2YBLpScxKNtsARAFgNtyQpzAwNSDHb0JA0w42IixFDY+KixNCENOtNibTejEjrzTGMwqbKgsTYNbQkPuUYGUCsRqDYWAA=”,
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [ #“Application Names” = _t, #“Attributes 1” = _t, #“Attributes 2” = _t, #“Attributes 3” = _t, #“Attributes 4” = _t, #“Attributes 5” = _t ]
),
ChangedType = Table.TransformColumnTypes (
Source,
{
{ “Application Names”, type text },
{ “Attributes 1”, type text },
{ “Attributes 2”, type text },
{ “Attributes 3”, type text },
{ “Attributes 4”, type text },
{ “Attributes 5”, type text }
}
),

Nagi_k · May 21, 2021, 3:50pm

Thanks @MK3010

Actually it is working ,
but if i have a date column in it and has some date format 1/1/2017 like this and that also has some blanks…
Due to which i am getting an error while working on it

Application Names	Server	Network	Storage	Inception Date	APM	Soemthing
dghewrhwh		ehllng			dfnjrt
h4hnD	hannpo	kjbklg	lkblkeg	1/1/2017	rjrej	rjer
rjre	l;lj;ge		kjngkln			rjrej
fdnteajm		Yes			rhj
djretj	nnlkdsg	Yes	rhrh		rejre	hrerhhr
drjeatj	segwrg	No	rehm	1/1/2017	rhreh	hrh
rjetj	geegew	Yes	hrreh		rhej	rherb
drjer	ghwrh	Yes	er		erjerj	jraetj
drjetjn	hwrh	No	hrreh	2/2/2021	reje5j	rheetj
rehrej	hrrwh	Yes	net		erjej	rhrhj
rjerj	hrwrh	No	rahreh		rej5e	rjrea
jertj	hw5		rnhreh			rehjr
jtejetj		Yes			hrej
jrej	rherwh	No	rntteh		r	ghre
rjtej	hrhjj		rhreh			rehjr
rejerj		reh			rh53y
erjrj		rhreh			fdhreh
rrw4rjn	erhe5	dherh	rehm		rehy4y	rhj5
rejreu	rejherj	rherh	hrea4		rh53y	rahjk
reure

AntrikshSharma · May 21, 2021, 4:03pm

@Nagi_k It is a bad practice to keep changing your requirement, everyone here are operating on their limited off work time! Make sure going forward you specify everything at once.

Nagi_k · May 21, 2021, 4:08pm

@AntrikshSharma , While i got the solution which you guys provided.

i didnt notice that i have a date in my columns.

Next time everything at once for sure …

AntrikshSharma · May 21, 2021, 4:19pm

No problem, it helps in keeping the posts short and to the point

Nagi_k · May 21, 2021, 4:21pm

i found the solution , i converted to dates to text and it worked

Thank you guys …