GreenBoy
New member
- Joined
- Apr 13, 2015
- Messages
- 26
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 365
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:
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
and the [ShortCode] in the seperate table is created using:
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
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:
Code:
= 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
Code:
= 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 seperate table is created using:
Code:
= 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