Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
7 | |
7 | |
6 | |
6 |