英文:
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
或者如果这会颠倒 TRUE
和 FALSE
值,只需更改为:
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]," > "), (n)=> Text.Start(n,2) = Text.Start([Input],2)
)
Or possibly better:
List.Count(
List.Distinct(Text.Split(Text.Remove([Input],{"0".."9"})," > "))
)>1
Or if this reverses the TRUE
and FALSE
values, just change to:
List.MatchesAny(
Text.Split([Input]," > "), (n)=> Text.Start(n,2) <> Text.Start([Input],2)
)
Or possibly better:
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))
Or, to reverse the logic again:
=MAP([Input],LAMBDA(n,SUM(N(LEFT(TEXTSPLIT(n," > "),2)<>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], " > "),
each Text.Start(_,2)
)
)
) > 1 then "True" else "False"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论