+ Reply to Thread
Results 1 to 9 of 9

power query filter by content of other column

  1. #1
    Registered User
    Join Date
    08-23-2022
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    66

    power query filter by content of other column

    Say I have two columns in Power query of 100 rows.

    Column A has numbers 1 till 10 in random order.
    Column B only has numbers 2 and 3 and some "null", in a different random order.
    Some rows have the same number in both columns, most do not.

    I want to see only those rows where

    Column A = 2 or 3
    Column A <> Column B

    I feel the solution would be to filter Column A by content of column B, then delete duplicate rows, but I do not know how to filter one column based on the content of another entire column.

    A B
    1 2
    4 3
    3 3
    5 5
    2 3

    only shows the last row.

    Hope thats clear!
    Attached Files Attached Files
    Last edited by TtNl; 11-03-2022 at 10:04 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: power query filter by content of other column

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: power query filter by content of other column

    like this?
    A
    B
    C
    1
    Column1 Custom A<>B
    2
    2
    3
    TRUE
    3
    3
    2
    TRUE


    with source
    A
    B
    1
    Column1 Custom
    2
    1
    2
    3
    1
    3
    4
    2
    2
    5
    2
    3
    6
    3
    2
    7
    3
    3
    8
    4
    2
    9
    4
    3
    10
    5
    2
    11
    5
    3
    12
    6
    2
    13
    6
    3
    14
    7
    2
    15
    7
    3
    16
    8
    2
    17
    8
    3
    18
    9
    2
    19
    9
    3
    20
    10
    2
    21
    10
    3

  4. #4
    Registered User
    Join Date
    08-23-2022
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    66

    Re: power query filter by content of other column

    Hi Sandy!

    Yes, exactly like that, I just now made an example but it's exactly what you did here.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: power query filter by content of other column

    sure, so here is
    Attached Files Attached Files
    Last edited by sandy666; 10-31-2022 at 02:05 PM.

  6. #6
    Forum Contributor
    Join Date
    02-25-2022
    Location
    Dallas, Texas
    MS-Off Ver
    Office 365
    Posts
    174

    Re: power query filter by content of other column

    Here is an example using SQL and querying workbook. The data is in the SourceExample.xlsx file. The code is in the ADODBFilterExample.xlsm workbook. It is extremely fast since it doesn't actually open the workbook. The query is extremely simple in your case but it can handle extremely complex queries. This is my go to method for extracting data. Here is the SQL

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mogul; 11-01-2022 at 11:22 AM.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: power query filter by content of other column

    thanks
    have a nice day

  8. #8
    Registered User
    Join Date
    08-23-2022
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    66

    Re: power query filter by content of other column

    Hi sandy,

    I tried to replicate what you did but it's hard for me to follow as you have "build a table inside power query" (don't know how to describe or if I'm right) it seems to me. It doesn't look complicated, but I have a hard time understanding referencing "list" and such.
    I will upload an example table, would you be willing to do the same steps on that?

    Also if I read this correctly you input manually to look for values "2" and "3". It wasn't clear from my example at all but actually I want to look for "any value" present in column B in column A. So 2/3 was an example, but with next refresh this could be 4 & 5 for example.

    @mogul, thanks for replying. I'm sure it's much more efficient and elegant but SQL is entirely new to me so I worry that my investment in that will take too much effort to get to where I want. Never heard of this so its definitely good to know this exists and what it is.

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: power query filter by content of other column

    create blank query then in formula bar type
    = {1..10}
    next from menu (List Tools) To Table (Convert)
    =Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    next Add Custom Column and type inside
    {2..3}
    Expand Custom to New Rows
    = Table.ExpandListColumn(List23, "Custom")
    add new custom column and type inside
    [Column1]<>[Custom]
    and next filter this column by TRUE
    [#"A<>B"] = true
    and add manually next two conditions
    and ([Column1] = 2 or [Column1] = 3)

    the last step is half manually all the rest is from menu

    whole M:
    Please Login or Register  to view this content.
    solution was tailored to the OP

    if you want something new , create new thread with detailed description

    probably it will be like this

    Please Login or Register  to view this content.
    Last edited by sandy666; 11-03-2022 at 10:39 AM. Reason: added code

+ 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. Issue with getting content from files using Power Query
    By Spocker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-01-2021, 03:03 PM
  2. [SOLVED] Power Query/Power Pivot Conditional Column creation & chart
    By mz_h in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-12-2020, 06:07 PM
  3. Replies: 6
    Last Post: 10-16-2020, 05:44 AM
  4. Power Query - Access Web content
    By csmourya in forum Excel General
    Replies: 1
    Last Post: 08-29-2020, 07:48 AM
  5. Replies: 10
    Last Post: 06-25-2020, 12:19 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 Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM

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