Forum Discussion
cgkas
Helper V
Helper VHow to get the Longest Common Prefix (LCP)?
Hi to all, I have the need to get the longest common prefix between values in 2 columns. LCP is implemented in several languages in this rosettacode link, but is there a built-in function in M la...
Here you go.
(input1 as text, input2 as text)=> let Inputs = {input1, input2}, SplitAndZip = List.Zip(List.Transform(Inputs, each Text.ToList(_))), CompareValues = List.Transform(SplitAndZip, each _{0} = _{1}), FindFirstFalse = List.PositionOf(CompareValues, false), KeepFirstCharacters = if input1=input2 then input1 else Text.Combine(List.FirstN(Text.ToList(Inputs{0}), FindFirstFalse), "") in KeepFirstCharactersPat
Here you go.
(input1, input2)=> let Inputs = {input1, input2}, SplitAndZip = List.Zip(List.Transform(Inputs, each try Text.ToList(_) otherwise {})), CompareValues = List.Transform(SplitAndZip, each _{0} = _{1}), FindFirstFalse = List.PositionOf(CompareValues, false), KeepFirstCharacters = if input1=input2 then input1 else Text.Combine(List.FirstN(Text.ToList(Inputs{0}), FindFirstFalse), "") in KeepFirstCharactersPat
ppm1
Solution Sage
Solution SageHere's a function that shows one way to do that with two text value inputs. Just create a blank query, open the advanced editor and replace the text with the M code below. You could then use the function in a custom column (for example) and provide the two text column names as inputs.
(input1 as text, input2 as text)=>
let
Inputs = {input1, input2},
SplitAndZip = List.Zip(List.Transform(Inputs, each Text.ToList(_))),
CompareValues = List.Transform(SplitAndZip, each _{0} = _{1}),
FindFirstFalse = List.PositionOf(CompareValues, false),
KeepFirstCharacters = Text.Combine(List.FirstN(Text.ToList(Inputs{0}), FindFirstFalse), "")
in
KeepFirstCharacters
Pat
cgkasHelper V
Helper Vppm1 I talk to much early. There is still an error when trying with actual data when input1 and/or input2 have "null" value.
I get this error.
Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]
The custom column I'm adding is like this
#"Added Custom" = Table.AddColumn(#"Step123", "Custom Col", each Text.Combine({[Col1],[Col2],fnMatchingPrefix([Col3],[Col4])})),
May you help me to fix this part in order that function ouputs empty values when Col3 and/or Col4 be null? Thanks
- ppm1
Here you go.
(input1, input2)=> let Inputs = {input1, input2}, SplitAndZip = List.Zip(List.Transform(Inputs, each try Text.ToList(_) otherwise {})), CompareValues = List.Transform(SplitAndZip, each _{0} = _{1}), FindFirstFalse = List.PositionOf(CompareValues, false), KeepFirstCharacters = if input1=input2 then input1 else Text.Combine(List.FirstN(Text.ToList(Inputs{0}), FindFirstFalse), "") in KeepFirstCharactersPat