Forum Discussion
Need help with Custom Column
Hi,
I need your help with the custom column formula for the below table. I am looking to create a table with a condition that at least any of two columns should have non zero values out of three region columns.
Thank you in advance
In the screenshort which you have shown only Europe column has non zero value. So for the records shown you will get FALSE. May be the qualifying records may be much down below which are not within PQ limit of 1000 records for preview.
You apply the filter to TRUE and load the records. Then those records will be visible to you. If you are interested in another construct, then below is another formula.
([EUROPE]>0 and [NORTH AMERICA]>0) or ([NORTH AMERICA]>0 and [ASIA PAC]>0) or ([EUROPE]>0 and [ASIA PAC]>0)
7 Replies
Use this
(Number.From([EUROPE]>0) + Number.From([NORTH AMERICA]>0) + Number.From([ASIA PAC]>0))>1Thanks for the reply. The result is all "Flase". I am sure there are records in the data which should have been "True".
Your thoughts?Copy this code in a fresh query and you will see that it works correctly.
Why it is not working for you - I would need to look at a sample file provided by you which you can upload to Onedrive/Google drive etc and share the link here
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSAWNDpVidaCAJ4RmbgLkQCZAQjGcGV2oBlTOCy0FwbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EUROPE = _t, #"NORTH AMERICA" = _t, #"ASIA PAC" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"EUROPE", Int64.Type}, {"NORTH AMERICA", Int64.Type}, {"ASIA PAC", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (Number.From([EUROPE]>0) + Number.From([NORTH AMERICA]>0) + Number.From([ASIA PAC]>0))>1) in #"Added Custom"
