• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

List.Contains issues|Power Query|Excel Forum|My Online Training Hub

You are here: Home / List.Contains issues|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryList.Contains issues
sp_PrintTopic sp_TopicIcon
List.Contains issues
Avatar
Dave Green
Member
Members
Level 0
Forum Posts: 25
Member Since:
January 7, 2012
sp_UserOfflineSmall Offline
1
July 1, 2018 - 4:04 am
sp_Permalink sp_Print

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:

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 separate 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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
July 1, 2018 - 6:10 pm
sp_Permalink sp_Print

Hi David,

Can you upload a sample file with your tables? Hard to see the problem without sample data.

Avatar
Dave Green
Member
Members
Level 0
Forum Posts: 25
Member Since:
January 7, 2012
sp_UserOfflineSmall Offline
3
July 1, 2018 - 7:26 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
July 2, 2018 - 2:26 pm
sp_Permalink sp_Print

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.
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Janset Beyaz
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27209

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.