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 language? or how can I do it?
For example I found a function to combine 2 texts like this:
= Text.Combine({"interspecies","-","interstellar"})
interspecies-interstellar
But how would be a function to get this common prefix for the same 2 strings?
= SomeFuntion({"interspecies","interstellar"})
"inters"
Solved! Go to Solution.
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
KeepFirstCharacters
Pat
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
KeepFirstCharacters
Pat
Thanks so much Pat, that fix the issue. Regards
Thanks so much for your help. It almost working, only detected one issue when compares 2 strings that are equal. In this case the expected output should be the same string and currently is showing blank. Please see table below. For example some comparisons below and current and expected output.
Input1 | Input2 | Current Output | Expected Output |
9000 | 9999 | 9 | 9 |
8549000 | 8549999 | 8549 | 8549 |
50 | 99 | ||
99999 | 99999 | 99999 |
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
KeepFirstCharacters
Pat
Thanks so much. This time it fits all conditions. I was trying to add in wrong place the "if else" you added. I see in your solution, the "if else" should go inside one step, not outside.
Here'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
@ppm1 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
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
KeepFirstCharacters
Pat