12947

Remove duplicates values based on multiple column with a condition in query editor Power BI

<h3>Question</h3>

I am new to power bi and would require your help to sort out below issue which I am facing.

Basically I am taking three columns into consideration as below:

<em>Question:</em> I would like to remove duplicate values from above table based on conditon " Equal value for "Time" ,"ID" and Absolute difference in "Time spent" is lower or equal than 1" as you can see in the image Rows highlighted falls in this category.

I would like to get these below rows removed based upon condition.

Question: I would like to remove duplicate values from above table based on conditon " Equal value for "Time" ,"ID" and Absolute difference in "Time spent" is lower or equal than 1" as you can see in the image Rows highlighted falls in this category.

I would like to get these below rows removed based upon condition.

I am able to perform this in excel by making us of a fourth column with formulae =IF(AND(A3=A2,B3=B2,ABS(F3-F2)<1),"problem",0) and then filtering out the rows marked as probelm. Please help!!

Regards

Mahi


<h3>Answer1:</h3>

I bet the suggestion from @Alexis Olson works just fine, but since you specifically mentioned the Query Editor, here's how I would do it there:

<hr /><ol><li>Have your data loaded like below, and just accept the changes made under Changed Type:</li> </ol>

Don't worry about the other steps under the Query Settings. We'll get to that eventually.

<ol start="2"><li>Select Add Column and click Index Column, so that you get this:</li> </ol>

<ol start="3"><li>Select Add Column, click Custom Column and insert this little formula in the appearing dialog box Table.AddColumn(#"Added Index", "Custom", each #"Added Index"[Time Spent]{[Index]}-#"Added Index"[Time Spent]{[Index]-1}):</li> </ol>

<ol start="4"><li>Click OK, and make sure that you're getting this:</li> </ol>

<ol start="5"><li>I think this step is a little weird, but you'll have to click 'Table' there in the column:</li> </ol>

<ol start="6"><li>You will get an Error message in the first row, but you can remove that by right-clicking that column, and clicking Remove Errors:</li> </ol>

<ol start="7"><li>Now you can click the drop-down menu in the Custom Column, select Number Filter and Does Not Equal</li> </ol>

<ol start="8"><li>And insert 0, or select 0 from the drop-down menu in the dialog box:</li> </ol>

<ol start="9"><li>This is it, your required numbers should now be filtered away:</li> </ol>

Note, however, that this procedure comes at a cost since you're losing the first value due to the first step in the indexing. If the rest of this is something you can use, I can see if we can fix that last little part as well.


<h3>Answer2:</h3>

You can pick a representative [Time Spent] value from each unique set of rows by taking a max or min over the list of "duplicate" values. Here's the formula for such a custom column, which I'll call [Min Time]:

= List.Min( Table.SelectRows(#"Previous Step", (C) => (C[Time] = [Time] and C[ID] = [ID] and Number.Abs(C[Time Spent] - [Time Spent]) < 1) )[Time Spent])

Once you have this custom column, you can group by [Time], [ID], and [Min Time] to roll up the duplicates and then rename the [Min Time] column to [Time Spent].

来源:https://stackoverflow.com/questions/51798712/remove-duplicates-values-based-on-multiple-column-with-a-condition-in-query-edit

Recommend

  • Remove duplicates values based on multiple column with a condition in query editor Power BI
  • How to work with bindings when using a view model vs using @Binding in the view itself?
  • Can't make use of different proxies to perform subsequesnt requests
  • How to handle words that are not in word2vec's vocab optimally
  • Django SECURE_SSL_REDIRECT breaks unit tests that use the in-built client
  • Combine two scatter plots with different data points
  • Building/deploying a EJB .jar with its dependencies
  • HTML5 Local Storage and Variable Types
  • Does Python have a linspace function in its std lib?
  • OverflowError 'Numerical result out of range' when generating fibonacci numbers [duplicate
  • What is difference between waitForAngularEnabled and browser.ignoreSynchronization in protractor?
  • Android Studio 3.0 - Settings are not saved
  • NSNonLossyASCIIStringEncoding returns nil
  • Haskell Time Limit on Evaluation
  • Write unittest for function with yield
  • Add Value to RoutValues and keep Current Values in ASP.NET MVC 3
  • Image gets distorted sometime while uploading
  • How can I do TDD and Unit Testing for EF Code First entity declaration and mapping?
  • Make ASP.NET web application FIPS Compliant?
  • How to implement NOT LIKE as the search condition for containstable(Full-Text Query)?
  • Running Applescript from a Cocoa application
  • Facebook Error (#200) The user hasn't authorized the application to perform this action (PHP)
  • How to load dynamic images in custom ListView
  • What is the difference between dynamically creating a script tag and statically embed a script tag?
  • How to warp text around image in iOS?
  • How to use FirstOrDefault inside Include
  • PHP Permalinks.. how to change?
  • Running R's aov() mixed effects model from Python using rpy2
  • How to mutate multiple variables without repeating codes?
  • ReferenceError: TextEncoder is not defined