+ Reply to Thread
Results 1 to 8 of 8

How to delete rows on Different criteria in Power Query

Hybrid View

  1. #1
    Registered User
    Join Date
    11-02-2022
    Location
    Pakistan
    MS-Off Ver
    Office 365
    Posts
    10

    Question How to delete rows on Different criteria in Power Query

    Hi everyone,
    I have three columns to in Excel file and I want the solution in Power Query.
    Here are the complete details:

    Column A Column B
    U UU
    U SIT
    U QI
    Z SIT
    V SIT
    V QI
    V UU
    Z2 SIT
    Z UU
    Z QI
    Z SIT
    Z2 QI
    Z QI
    Z2 SIT
    Z2 SIT
    Z QI
    Z2 SIT


    I need to remove only specific rows where Column A contains Z and Z2 and column B contains QI and UU.
    In other words I just want to keep all vaue for column A except for Z and Z2 I need only SIT value in column B. Please help me to solve this issue in Power Query.

    Thanks,
    Rose
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,406

    Re: How to delete rows on Different criteria in Power Query

    Power Query
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Filtered Rows" = Table.SelectRows(Source, each [Column A] <> "Z" and [Column A] <> "Z2" and [Column B] <> "UU" and [Column B] <> "QI")
    in
        #"Filtered Rows"
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-02-2022
    Location
    Pakistan
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How to delete rows on Different criteria in Power Query

    This is not what I want, I just want to delete Z and Z2 in column A which contains UU and QI and all the other data should come like in column A we have U, Z and V this data should come

  4. #4
    Registered User
    Join Date
    11-02-2022
    Location
    Pakistan
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How to delete rows on Different criteria in Power Query

    This is not what I want, I just want to delete Z and Z2 in column A which contains UU and QI and all the other data should come like in column A we have U, Z and V this data should come

  5. #5
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: How to delete rows on Different criteria in Power Query

    It is also possible with a formula.

    Please try
    Formula: copy to clipboard
    =FILTER(A2:B18,((A2:A18<>"Z")*(A2:A18<>"Z2"))+((B2:B18<>"QI")*(B2:B18<>"UU")))
    Attached Files Attached Files

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

    Cool Re: How to delete rows on Different criteria in Power Query

    maybe this

    Column A Column B Column A Column B
    U UU U UU
    U SIT U SIT
    U QI U QI
    Z SIT Z SIT
    V SIT V SIT
    V QI V QI
    V UU V UU
    Z2 SIT Z2 SIT
    Z UU Z SIT
    Z QI Z2 SIT
    Z SIT Z2 SIT
    Z2 QI Z2 SIT
    Z QI
    Z2 SIT
    Z2 SIT
    Z QI
    Z2 SIT


    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Merge = Table.CombineColumns(Source,{"Column A", "Column B"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged"),
        TSR = Table.SelectRows(Merge, each ([Merged] <> "Z2=QI" and [Merged] <> "Z=QI" and [Merged] <> "Z=UU")),
        Split = Table.SplitColumn(TSR, "Merged", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), Table.ColumnNames(Source))
    in
        Split

  7. #7
    Registered User
    Join Date
    11-02-2022
    Location
    Pakistan
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How to delete rows on Different criteria in Power Query

    Yes, this is I want, Thanks a Bunch !

  8. #8
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,406

    Re: How to delete rows on Different criteria in Power Query

    Or so?
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [Column B] = "SIT" then [Column B] else null),
        #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = "SIT")),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
    in
        #"Removed Columns"
    Attached Files Attached Files

+ 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] Delete null cells in Power query
    By Undo in forum Excel General
    Replies: 9
    Last Post: 06-19-2022, 02:01 PM
  2. Replies: 1
    Last Post: 11-14-2021, 01:06 PM
  3. Replies: 1
    Last Post: 05-07-2021, 05:40 AM
  4. Extract specific rows based on criteria in power query
    By ronakchoudhary in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-31-2020, 05:02 PM
  5. [SOLVED] Power query header row delete
    By pdig in forum Excel General
    Replies: 3
    Last Post: 03-22-2019, 05:51 AM
  6. Power Query - Remove Specific Duplicate Rows (Based on sort criteria)
    By kersplash in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-22-2019, 03:30 AM
  7. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 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