CODEWITHSUNDEEP
excelexcel-formulapowerbipowerquery
Olendris63
Olendris63

Reputation: 97

power query to return true / false if multiple columns are either = or null

Here's what I am trying to do.

I need a query to figure out if a worker has moved state in the last month.

the columns I'm working with show state as a 2-digit EX: TX

the provincial move data all show in 1 column formatted as seen below:

enter image description here

I've tried separating the 1 column by delimiter which would give as many new columns as there are delimiters. then tried inserting a custom column to return (true/false) if there were any changes, but no luck...

what I'd be looking for is a simple power query code that would return any and all changes as true per the example below:

if there were any changes to the 2-digit state code, it should return "True". and "False" only if there were no changes to the 2-digit state codes.

enter image description here

Upvotes: 0

Views: 1011

Answers (1)

horseyride
horseyride

Reputation: 21318

In powerquery, add column, custom column with formula

= try if List.Count(List.Distinct(Text.Split([YourColumnNameHere]," > "))) =1 then false else true otherwise true

enter image description here

In excel, something like this, which someone will add a more compact version of

=IFERROR(NOT(SUBSTITUTE(SUBSTITUTE(A1," > ",",")&",",LEFT(SUBSTITUTE(A1," > ",",")&",",FIND(",",SUBSTITUTE(A1," > ",","))),"")=""),TRUE)

enter image description here

Upvotes: 0

Related Questions