将Power Query中的自定义列转换为真/假语句

huangapple go评论72阅读模式
英文:

Power Query transform custom column into T/F statement

问题

"True" 如果至少有一个不同省份的实例,"False" 如果它们都是相同的省份。

英文:

I have an Excel report that returns a column with worker's province changes over time coded like the following:
SK36 > SK30 > AB01 where only the first 2 letters matter (province codes) and the 2 numbers after the letters don't matter. there can be up to 10 codes in the same cell like: ON## > ON## > BC## > BC## > BC##....

what I'm looking for is a query that would return "True" if there is at least 1 instance of a different province, and "False" if they are all the same provinces:

AB01 > AB55 > AB75 = True

SK36 > SK30 > AB01=False

答案1

得分: 5

PowerQuery:

List.MatchesAll(
    Text.Split([Input], " > "), (n) => Text.Start(n, 2) = Text.Start([Input], 2)
)

或者更好的方式可能是:

List.Count(
    List.Distinct(Text.Split(Text.Remove([Input], {"0".."9"}), " > "))
) > 1

或者如果这会颠倒 TRUEFALSE 值,只需更改为:

List.MatchesAny(
    Text.Split([Input], " > "), (n) => Text.Start(n, 2) <> Text.Start([Input], 2)
)

或者更好的方式可能是:

List.Count(
    List.Distinct(Text.Split(Text.Remove([Input], {"0".."9"}), " > "))
) = 1

Formula:

=MAP([Input],LAMBDA(n,SUM(N(LEFT(TEXTSPLIT(n, " > "), 2) <> LEFT(n, 2))) > 0)

或者,要再次颠倒逻辑:

=MAP([Input],LAMBDA(n,SUM(N(LEFT(TEXTSPLIT(n, " > "), 2) <> LEFT(n, 2))) = 0)
英文:

PowerQuery:

List.MatchesAll(
    Text.Split([Input],&quot; &gt; &quot;), (n)=&gt; Text.Start(n,2) = Text.Start([Input],2)
)

Or possibly better:

List.Count(
    List.Distinct(Text.Split(Text.Remove([Input],{&quot;0&quot;..&quot;9&quot;}),&quot; &gt; &quot;))
)&gt;1

Or if this reverses the TRUE and FALSE values, just change to:

List.MatchesAny(
    Text.Split([Input],&quot; &gt; &quot;), (n)=&gt; Text.Start(n,2) &lt;&gt; Text.Start([Input],2)
)

Or possibly better:

List.Count(
    List.Distinct(Text.Split(Text.Remove([Input],{&quot;0&quot;..&quot;9&quot;}),&quot; &gt; &quot;))
)=1

Formula:

=MAP([Input],LAMBDA(n,SUM(N(LEFT(TEXTSPLIT(n,&quot; &gt; &quot;),2)&lt;&gt;LEFT(n,2)))&gt;0))

Or, to reverse the logic again:

=MAP([Input],LAMBDA(n,SUM(N(LEFT(TEXTSPLIT(n,&quot; &gt; &quot;),2)&lt;&gt;LEFT(n,2)))=0))

答案2

得分: 4

这个自定义列将完成以下工作:

如果 List.Count(
List.Distinct(
List.Transform(
Text.Split([YourColumnName], " > "),
each Text.Start(_,2)
)
)
) > 1 则返回 "True" 否则返回 "False"

英文:

this custom column will do the work:

if List.Count(
    List.Distinct(
        List.Transform(
            Text.Split([YourColumnName], &quot; &gt; &quot;), 
            each Text.Start(_,2)
        )
    )
) &gt; 1 then &quot;True&quot; else &quot;False&quot;

huangapple
  • 本文由 发表于 2023年6月5日 23:13:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/76407816.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定