+ Reply to Thread
Results 1 to 15 of 15

Power Query Filter by list for each column using Accumulate

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    649

    Power Query Filter by list for each column using Accumulate

    ]

    Hi,
    I want the filter by text in a string for each column of a table so to filter by one column one word;

    Please Login or Register  to view this content.

    From this I did a manual version for each column ;

    Please Login or Register  to view this content.

    and then to accumulate ;

    L
    Please Login or Register  to view this content.
    but if I want to filter by a list so ; { "cat", "dog"} ,I think I need to break into a list so ;

    Please Login or Register  to view this content.
    and then
    Please Login or Register  to view this content.
    so for this to work for each column I need to first get each as a list and then true /false which I have done, and got stuck;
    I want to use each true false as a filter

    Please Login or Register  to view this content.
    So I want to filter each row based on if any part as true / false, so all for each row have to be one or the other,
    but not sure how to proceed, whether I need to wrap in another accumulate or not;

    I have included workbook with all working out up to now ; the 'list accumulate' query is as far as I have got;

    Richard
    Attached Files Attached Files

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,300

    Re: Power Query Filter by list for each column using Accumulate

    So as a result, you want any rows where no column contains any of the words in the list?
    Anyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    649

    Re: Power Query Filter by list for each column using Accumulate

    Sorry for slow response essentially yes, that's correct,


    I've done a bit more ;

    Please Login or Register  to view this content.

    So in this example attached, I have a column with lists of true false, so my next step is to get whether any list contains true

    so for this as an example only row four should remain or not when filtered down.

    I'm just using the add column at the moment as it can be easier to see what's happening

    Richard.
    Attached Files Attached Files

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,300

    Re: Power Query Filter by list for each column using Accumulate

    Doesn't your Table1 (2) query already do what you want?

  5. #5
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    649

    Re: Power Query Filter by list for each column using Accumulate

    No that works for one value not filtering by a list ; I've got a bit further ;

    I found a function list.anytrue , so now I have a column that could be used,

    Please Login or Register  to view this content.

    So these true / false I want to pass directly to table . select rows, rather than have the helper, I've attached amended.

    RD
    Attached Files Attached Files

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,300

    Re: Power Query Filter by list for each column using Accumulate

    You could just use List.ContainsAny as you have there instead of your Text.Contains in the Table1(2) query.

  7. #7
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    649

    Re: Power Query Filter by list for each column using Accumulate

    The problem is splittign each text value into a list ;


    so this is it manually;
    Please Login or Register  to view this content.

    The problem is the text.split, I can't come up with a way to get that into an accumulation. if you use Table.Column( S,C), you get a list
    not each string to be splt, I did just try C from (S,C)=> but this give the actual text ie "One" .

    So this would be for one column ;

    Please Login or Register  to view this content.
    😣

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,913

    Re: Power Query Filter by list for each column using Accumulate

    Why List.Accumulate?

    Quickest way to do this is to concatenate all columns of row. Then split by list of criteria and count resulting list. If = 1 then keep.
    So something like...
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  9. #9
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,300

    Re: Power Query Filter by list for each column using Accumulate

    Doesn't this do it the way you want:

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    649

    Re: Power Query Filter by list for each column using Accumulate

    That's a pretty good approach, and certainly works, one thing with the accumulation, as used in the text .contains example,

    you can control filtering for or filtering out something by use 'not' or not using 'not' , but it's certainly the best so far, I have been,

    thankyou, I will leave it open for a bit longer see if anyone else has any ideas.

    thanks again,

    Richard.

  11. #11
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    649

    Re: Power Query Filter by list for each column using Accumulate

    Yes, I really thought I'd tried every combination , I think I kept using split without table column or table column without text split,

    thanks once again,

    I'll mark this up.


    Richard.

  12. #12
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    649

    Re: Power Query Filter by list for each column using Accumulate

    On a similar theme to yours; I did come up with this;

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    649

    Re: Power Query Filter by list for each column using Accumulate

    Hello Rory,

    Bit confused, the Table.Colunn returns a list , not text, or is that the function (A)=> is return one row so a record that acts as a tablle?
    or is that it is within the list.accumulate function?

    RD

  14. #14
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,300

    Re: Power Query Filter by list for each column using Accumulate

    Table.SelectRows iterates row by row so A is a record, which gets treated as a table.

  15. #15
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    649

    Re: Power Query Filter by list for each column using Accumulate

    Thanks, I thought it was due to the table.selectrows, it's just not easy to visualize what you are going to get until it's all in place.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Filter a Custom Column in Power Query
    By chi05 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-11-2023, 03:05 PM
  2. [SOLVED] [Power Query] How to filter 0 from two columns in power query editor in one step?
    By daliye in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-16-2023, 09:44 PM
  3. Replies: 2
    Last Post: 07-14-2023, 08:21 AM
  4. Replies: 0
    Last Post: 05-24-2023, 12:39 PM
  5. power query filter by content of other column
    By TtNl in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-03-2022, 10:28 AM
  6. [SOLVED] Power Query Filter Column
    By bfaws in forum Excel General
    Replies: 3
    Last Post: 02-26-2020, 11:09 AM
  7. [SOLVED] Power Query / Get & Transform - filter text contains based on list
    By SHI.NL in forum Excel General
    Replies: 8
    Last Post: 05-14-2018, 04:53 PM

Tags for this Thread

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