If Null Condition in Table.Group always failing

[GInNeed]

Hi,

I am trying to get the count of photos in a column using Power Query.

I have a table as follows (example):

ID	ItemName	ItemPhotoName	ItemPhotoFileType
1001	Tv	Photo.jpg	.jpg
1001	Tv	Photo2.png	.png
1002	Xbox	null	null

In this case, ID 1002 had an item listed but no photos were added.

In order to count the photos per item I've used:

= Table.Group(#"Previous Table", {"ID", "ItemName"}, {{"PhotoPerItemCount", each if [ItemPhotoName] = null then 0 else Table.RowCount(_)}})

The results are:

ID	ItemName	PhotoPerItemCount
1001	Tv	2
1002	Xbox	1

On the most part this works.. however for the case of ID 1002, the PhotoPerItemCount is still showing as 1, rather than falling in to the true statement of the IF Statement ( and showing as 0)
I've tested this elsewhere, and the IF Statement is fine, it just seems to have an issue when being used in the Table.Group. Perhaps it's my misunderstanding of how Table.Group is working, but I can't understand why the IF Statement would fall into the False condition.

Does anyone know why this happens and knows a solution for the above problem?

Thanks in advance.

[wdx223_Daniel]

= Table.Group(#"Previous Table", {"ID", "ItemName"}, {{"PhotoPerItemCount", each List.NonNullCount([ItemPhotoName])}})

[GInNeed]

@wdx223_Daniel This seems to work, so thank you! But I'm unsure why it needs to be done this way, compared to my original solution?

Do you know?

[wdx223_Daniel]

this "each" bring in a group, which is grouped by the columns in the 2nd parameter.

so [ItemPhotoName] represent a list, and it can not compare to 0 directly, you need it iterate over every item in the list to compare to null, such as "List.Tranform([ItemPhotoName],each if _= null then 0 else 1"

[v-yanjiang-msft]

Hi @GInNeed ,

You should replace [ItemPhotoName] with _[ItemPhotoName]{0} in the code.

Additionally, null is generally present blank in a number type column, apparently the ItemPhotoName column is of text type.

If it is really text null in the cell, this works fine.

= Table.Group(#"Changed Type", {"ID", "ItemName"}, {{"PhotoPerItemCount", each if _[ItemPhotoName]{0} = "null" then 0 else Table.RowCount(_)}})

Result:

Here's the whole M syatax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUQopAxIBGfkl+XpZBelANpiK1cGiwEivIA+sAkRBVRgB+RFJ+RVAKq80JwdGxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ItemName = _t, ItemPhotoName = _t, ItemPhotoFileType = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ItemName", type text}, {"ItemPhotoName", type text}, {"ItemPhotoFileType", type text}}),
    #"Group"= Table.Group(#"Changed Type", {"ID", "ItemName"}, {{"PhotoPerItemCount", each if _[ItemPhotoName]{0} = "null" then 0 else Table.RowCount(_)}})
in
    #"Group"

If it is blank, modify it to:

= Table.Group(#"Changed Type", {"ID", "ItemName"}, {{"PhotoPerItemCount", each if _[ItemPhotoName]{0} = "" then 0 else Table.RowCount(_)}})

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GInNeed]

@v-yanjiang-msft Hi, thank you for the detailed explanation. But I have a few questions.

So my null is null, not "null". That is of course, unless the type conversion is converting it from null to "null" without me knowing it?

My questions are as follows:

- You state that I need to append '{0}' to the end, for what reason do I need to do this and what is it actually doing?
- In your solution you state I should change it to 

_[ItemPhotoName]{0} = ""

would not leaving it as null suffice? Or is there a function for checking null or "" (like ISBLANK)?
- With you mentioning that the type of the field is of type Text, is that a problem? Would I need to explicitly change it to some form of nullable Text, as I thought Text would be nullable by default?


Sorry for all the questions, I'm quite new to PowerBi so trying to understand a solution, as much as use it. Thanks in advance

[v-yanjiang-msft · Accepted solution]

Hi @GInNeed ,

For your first question, let me explain more about _[ItemPhotoName]{0}.

_ represent the all the tables you merged. You can modify the code like below to verify, click the Table cell,

You will see the contens in it.

_[ItemPhotoName] represent the [ItemPhotoName] column in the merged table. Also verify like this:

You can see it's a list instead of a value, to convert to a value, you should specify which row you want to get. For the ID 1002, there is only one row, the symbol {} can specify row in the list, {0} means the first row, {1} means the second row, and so on.

Modify the formula to verify:

2. You don't need to change the data type of the column, as I don't know the actually data, I give the solution according to situation.

If it's null:

= Table.Group(#"Changed Type", {"ID", "ItemName"}, {{"PhotoPerItemCount", each if _[ItemPhotoName]{0} = null then 0 else Table.RowCount(_)}})

If it's "null":

= Table.Group(#"Changed Type", {"ID", "ItemName"}, {{"PhotoPerItemCount", each if _[ItemPhotoName]{0} = "null" then 0 else Table.RowCount(_)}})

If it's blank:

= Table.Group(#"Changed Type", {"ID", "ItemName"}, {{"PhotoPerItemCount", each if _[ItemPhotoName]{0} = "" then 0 else Table.RowCount(_)}})

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AntrikshSharma]

@GInNeed Is the null in the data as a text "null" or just null representing nothing? if "null" then use this otherwise just remove the GetNulls step.

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WMjQwMFTSUQopAxIBGfkl+XpZBelANpiK1cGiwEivIA+sAkRBVRgB+RFJ+RVACoRiYwE=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ ID = _t, ItemName = _t, ItemPhotoName = _t, ItemPhotoFileType = _t ]
    ),
    GetNulls = 
        Table.TransformColumns (
            Source,
            {
                { "ItemPhotoName", each Replacer.ReplaceValue ( _, "", null ), type text },
                { "ItemPhotoFileType", each Replacer.ReplaceValue ( _, "", null ), type text }
            }
        ),
    GroupedRows = 
        Table.Group (
            GetNulls,
            { "ID", "ItemName" },
            {
                {
                    "PerItemCount",
                    each 
                        if List.NonNullCount ( [ItemPhotoName] ) > 0 
                        then Table.RowCount ( _ ) 
                        else 0,
                    Int64.Type
                }
            }
        )
in
    GroupedRows

[GInNeed]

@AntrikshSharma the value is specifically null, not "null". So GetNulls would not be needed.

That being said, I'm not sure why List.NonNullCount is needed, rather than the original solution?

Thanks.

[AntrikshSharma]

@GInNeed I used List.NonNullCount because my code doesn't depend on any other helper column, all I need to do is check if the current group's every row in [ItemPhotoName] is not null then give me table count otherwise I know it is a null so just a 0.