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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GInNeed
Regular Visitor

If Null Condition in Table.Group always failing

Hi,

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

I have a table as follows (example):

IDItemNameItemPhotoNameItemPhotoFileType
1001TvPhoto.jpg.jpg
1001TvPhoto2.png.png
1002Xboxnullnull


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:

IDItemNamePhotoPerItemCount
1001Tv2
1002Xbox1

 

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.

1 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,

vkalyjmsft_0-1668130532117.png

You will see the contens in it.

vkalyjmsft_1-1668130558958.png

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

vkalyjmsft_2-1668131136199.png

vkalyjmsft_3-1668131145864.png

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.

vkalyjmsft_4-1668131363883.png

Modify the formula to verify:

vkalyjmsft_5-1668131511494.png

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.

View solution in original post

9 REPLIES 9
wdx223_Daniel
Super User
Super User

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

@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_0-1668124948830.png

 

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
Community Support
Community Support

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:

vkalyjmsft_0-1668069804134.png

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.

@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

 

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,

vkalyjmsft_0-1668130532117.png

You will see the contens in it.

vkalyjmsft_1-1668130558958.png

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

vkalyjmsft_2-1668131136199.png

vkalyjmsft_3-1668131145864.png

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.

vkalyjmsft_4-1668131363883.png

Modify the formula to verify:

vkalyjmsft_5-1668131511494.png

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
Community Champion
Community Champion

@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

 

@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.

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors