Log In
Register
Home
January 7, 2012
Offline
Hi
I am having a real problem getting my head around the List.Contains function.
What I am trying to do is use a list stored in #"MyExclusions" called [ShortCode] to exclude the inclusion of pricing in a calculated column in my main table / query called (imaginatively i think) "MainTable"
The exclusions are pulled from the #"MainTable" column [ShortID] and if they match then I want the rates in column [Current Rates] to be left as null otherwise pull in the rates.
This is the core of the issue, which I need to embed in a nested if statement that pulls rates from other Rates columns based on BatchNr etc... but for now this has me stumped.
The code I have written is:
= Table.AddColumn(#"Expanded AllRates1", "Custom.1", each List.ContainsAny([ShortID],#"MyExclusions",[ShortCode]))
Now to clarify a couple of points.
The ShortID codes are derived from the following, which was created by PQ using the Add New Column from Examples
= Table.AddColumn(#"Reordered Columns", "Custom", each Splitter.SplitTextByDelimiter("-", QuoteStyle.None)(Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Billing Definition Name]){0}?){0}?, type text)
and the [ShortCode] in the separate table is created using:
= Table.AddColumn(#"Changed Type", "ShortCode", each Text.Start([Bill Def Name], 4), type text)
So - question is, is this 'format' issue in that the codes in one table dont match the other in format? They look the same, and do not appear to have any extraneous characters in the text.
Any help is gratefully received.
Thanks
David
November 8, 2013
Offline
Hi David,
Can you upload a sample file with your tables? Hard to see the problem without sample data.
January 7, 2012
Offline
Hi Catalin, thank you for the response
Please see the attached which is a sample file taken from a much larger workbook - i have removed a lot of columns to make it easier to understand
Both tables are on the one sheet in this example, and I have added a GOAL column with some notes of how things are derived.
Please let me know if you need anything else
And thank you very much for giving this some of your valuable time, it is much appreciated.
Thanks
David
November 8, 2013
Offline
Hi David,
The syntax you used or List.ContainsAny does not seem to be right:
List.ContainsAny([ShortID],#"MyExclusions",[ShortCode]) The function as indeed 3 arguments:
List.ContainsAny(list as list, values as list,optional equationCriteria as any) as logical
But seems that you're passing wrong values to it.
First argument needs to be a list, [ShortID] is the VALUE of the current row, not a list. The second argument must be a list, #"MyExclusions" looks more like a table to me, without seeing that step.
The third argument is optional, the function expects an equation criteria, you passed a column to this argument.
Try:
List.ContainsAny(#"MyExclusions"[ShortCode], {[ShortID]}))
Note the curly brackets that converts the current row value from ShortID column to a list, even if it's a single value it needs to be a list.
1 Guest(s)
