Forum Discussion
If Null Condition in Table.Group always failing
Hi Anonymous ,
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community SupportHi Anonymous ,
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.
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
Hi Anonymous ,
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.