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 ChampionAnonymous 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.
- AntrikshSharma
Anonymous 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.