cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Removing Records based on Conditions from two Different Columns

Hi All,

 

Please can somebody tell me how to do the attached in power query? I need to remove the quotes which have SO No and Opp records, if one are the other then keep the quotes.

 

Removing Rows.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Removing Records based on Conditions from two Different Columns

Hi @HabibAdil ,

Please try like this:

1.gif

For more details, please see the attachment.

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Super User VII
Super User VII

Re: Removing Records based on Conditions from two Different Columns

@HabibAdil here is an idea of how you can do it, you can tweak it as per your need. Add new blank query and in advanced editor paste the code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lECYaVYHSgPiBwhPCMg0wguZwyRc1aKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Quote = _t, SoNo = _t, Opp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quote", type text}, {"SoNo", Int64.Type}, {"Opp", type text}}),
    #"Opp" = Table.SelectRows ( #"Changed Type", each [Opp] <> null and [Opp] <>""),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Quote"}, Opp, {"Quote"}, "Opp.1", JoinKind.LeftOuter),
    #"Removed Duplicates" = Table.Distinct(#"Merged Queries", {"Quote"}),
    #"Expanded Opp.1" = Table.ExpandTableColumn(#"Removed Duplicates", "Opp.1", {"Opp"}, {"Opp.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Opp.1", each [SoNo] = null or [Opp.2] = null ),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Opp.2"})
in
    #"Removed Columns"

 

 

I would 💖 Kudos 🙂 if my solution helped. 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper III
Helper III

Re: Removing Records based on Conditions from two Different Columns

Hi @parry2k  Thanks for your reply. I am afraid I could not make it work. Just to mention I am new to power BI/Query. Please can you change your code to make it work for this?

The source table name is Quotes and it has the following fields.

Quote_No                          text

Quote_Version                  Whole Number

SO_No                              text

Opportunity_GUID           text

Highlighted
Community Support
Community Support

Re: Removing Records based on Conditions from two Different Columns

Hi @HabibAdil ,

Please try like this:

1.gif

For more details, please see the attachment.

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper III
Helper III

Re: Removing Records based on Conditions from two Different Columns

Hi @v-xuding-msft Thanks for your help!!!!! It worked perfectly.

Highlighted
Community Support
Community Support

Re: Removing Records based on Conditions from two Different Columns

😊I'm so happy that it works for you.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors