cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
estuelke
New Member

Removing Items from List if comparison criteria on next value(s) not met

I have a list of numbers and I need to ensure that each number is at least 2 * more than the next number and remove it if it's not.

 

For example, I have this list of numbers.  The orange highlighted numbers need to be removed from the list:

 

image.png

 

This is what I've got so far, but I think the last step brings me right back to my original problem so I'm stuck again.

 

#"List Unique Values" = List.Sort(List.Distinct(Table.Column(#"Table1","Value")), Order.Descending),
#"Table Unique Values" = Table.FromList(#"List Unique Values",Splitter.SplitByNothing(),{"Value"}),
#"Add Values List to Table" = Table.AddColumn(#"Table Unique Values", "Values", each #"List Unique Values"),
#"Expanded Values" = Table.ExpandListColumn(#"Add Values List to Table", "Values"),
#"Compare" = Table.AddColumn(#"Expanded Values","Compare", each if [Value] = [Values] then true else if [Value] > 2 * [Values] then true else false),

Any thoughts?

 

Thanks!

1 ACCEPTED SOLUTION

Hi @Nathaniel_C 

 

I was actually just looking at that link!  It ultimately helped me come up with the following solution:

 

 

(values as list) as list =>
let
    sorted = List.Sort(List.Distinct(values), Order.Descending),
    
    comparingValues = (n as number, valuesToCompare as list) =>
        let
            valueToCompare = valuesToCompare{n},
            Nullify = List.Transform(List.Sort(List.Distinct(valuesToCompare), Order.Descending), 
                each 
                if _ = null then null
                else if _ = valueToCompare then _ 
                else if _ > 2 * valueToCompare then _ 
                else if _ < valueToCompare/2 then _
                else null
            ),
            RemoveNulls = List.RemoveItems(Nullify, {null})
        in 
            RemoveNulls,

    finalValues = List.Generate(
        () => [n=0, myList = sorted, count=List.Count(sorted)],
        each [n] <= [count],
        each [n=[n]+1, myList=comparingValues([n], [myList]), count=List.Count([myList])]
    )
in
    finalValues

 

I orginally tried not having the count variable in the List.Generate function but it kept throwing a "not enough elements in the enumeration" error.

 

Works great!

 

View solution in original post

5 REPLIES 5
Nathaniel_C
Super User
Super User

Hi @estuelke ,

Not sure of your logic, but this is what your sentence says to me:

"I have a list of numbers and I need to ensure that each number is at least 2 * more than the next number and remove it if it's not."

So we start with the first number and compare it to the next number. Then the first number must be more than 2* greater than the next number then remove " it". What is "it"? the first number or the second?

 

49.97 *2 =    99.94 right? So remove the rest of the list? Because each number that is removed, then the first number that is left is then the next number to compare.

 

Give us more explanation please,

 

Nathaniel





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

Proud to be a Super User!




Hi @Nathaniel_C ,

 

I want to compare the first number to the second number.  If the first is not 2 * greater than the second, remove the second and keep comparing down the list removing numbers until I find a number where the first is 2 * greater.   

 

Then I want to traverse the newly updated list again, but starting with the second number, and so on...

 

So the end result would be a list where each number is at least 2 * greater than the one that follows.

 

 

 

 

Hi @estuelke ,

 

Wow! Thanks for this opportunity! As you described the issue, my background in programming said that sounds like a loop.  Don't think so...however this blog post describes how to create a loop in M language. https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/

 

Do you have programming background? Try it and let me know. Otherwise if I have some time, I will try it this weekend.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




Hi @Nathaniel_C 

 

I was actually just looking at that link!  It ultimately helped me come up with the following solution:

 

 

(values as list) as list =>
let
    sorted = List.Sort(List.Distinct(values), Order.Descending),
    
    comparingValues = (n as number, valuesToCompare as list) =>
        let
            valueToCompare = valuesToCompare{n},
            Nullify = List.Transform(List.Sort(List.Distinct(valuesToCompare), Order.Descending), 
                each 
                if _ = null then null
                else if _ = valueToCompare then _ 
                else if _ > 2 * valueToCompare then _ 
                else if _ < valueToCompare/2 then _
                else null
            ),
            RemoveNulls = List.RemoveItems(Nullify, {null})
        in 
            RemoveNulls,

    finalValues = List.Generate(
        () => [n=0, myList = sorted, count=List.Count(sorted)],
        each [n] <= [count],
        each [n=[n]+1, myList=comparingValues([n], [myList]), count=List.Count([myList])]
    )
in
    finalValues

 

I orginally tried not having the count variable in the List.Generate function but it kept throwing a "not enough elements in the enumeration" error.

 

Works great!

 

@estuelke ,

Sweet! It is amazing how much info is out there!

Nathaniel





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

Proud to be a Super User!




Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.