Results 1 to 12 of 12

PQ FindText exact matching columnname

Threaded View

  1. #5
    Forum Contributor
    Join Date
    04-30-2010
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    215

    Re: PQ FindText exact matching columnname

    Table looks like this:
    AddNonExistingColumns.png

    I need to check if there already is an existing column named "Colors", if so, it should do nothing, otherwise it should add a new column named "Colors".

    Then I need it to do the same with columns "GreenFruits" and "Fruits".

    This is the code is use in Table (2):
    let
       Source = Table,
       Colors = if List.Count(List.FindText(Table.ColumnNames(Source), "Colors" ))= 1 then Source else Table.AddColumn(Source, "Colors", each "", type text),
       GreenFruits = if List.Count(List.FindText(Table.ColumnNames(Colors), "GreenFruits" ))= 1 then Colors else Table.AddColumn(Colors, "GreenFruits", each "", type text),
       Fruits = if List.Count(List.FindText(Table.ColumnNames(GreenFruits), "Fruits" ))= 1 then GreenFruits else Table.AddColumn(GreenFruits, "Fruits", each "", type text)
    
    in
        Fruits
    The first step goes well. List.Count(List.FindText(Table.ColumnNames(Source), "Colors" )) is 1, so it does nothing.
    The second step goes well. List.Count(List.FindText(Table.ColumnNames(Colors), "GreenFruits" )) is 0, so it creates the column named "GreenFruits".
    The last step does not go well. List.Count(List.FindText(Table.ColumnNames(GreenFruits), "Fruits" )) counts 3, as it finds both "Fruits", "GreenFruits" and "BlueFruits". Which means it tries to create a new column called "Fruits". But that one already exists, so it returns this error:
    AddNonExistingColumns2.png

    Instead of getting 3 counts on "Fruits", ie "Fruits", "GreenFruits" and "BlueFruits", I need it to only get 1 count, ie an exact match for "Fruits".

    The expected result should be 4 columns in the end:
    Fruits BlueFruits Colors GreenFruits
    Last edited by Imbizile; 03-30-2021 at 05:36 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VLOOKUP & Table ColumnName
    By dageci in forum Excel General
    Replies: 2
    Last Post: 06-02-2019, 10:32 AM
  2. [SOLVED] Find exact text in exact cell but on any sheet?
    By nobodyukno in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2017, 02:59 PM
  3. Replies: 4
    Last Post: 01-06-2015, 09:15 PM
  4. To Sort exact and partial exact match for a single column.
    By Jagdev in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2014, 05:08 AM
  5. Formula to work out an exact average over an exact number
    By Sandyshirl in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-11-2013, 01:35 AM
  6. SQL against Excel Table returns nothing when "Where [ColumnName] = ""
    By rgi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-19-2010, 04:21 PM
  7. FindText Extraction?
    By flembss2 in forum Excel General
    Replies: 4
    Last Post: 06-03-2008, 04:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1