question

ErikBohn-1476 avatar image
0 Votes"
ErikBohn-1476 asked Lz-3068 answered

PowerQuery Table.FuzzyJoin SimilarityColumnName index out of range

Hi All,

When using Table.FuzzyJoin and specifying the optional argument SimilarityColumnName I get :

 Unexpected error: Index was outside the bounds of the array.
 Details:
     Microsoft.Mashup.Evaluator.Interface.ErrorException: Index was outside the bounds of the array. ---> System.IndexOutOfRangeException: Index was outside the bounds of the array. ---> System.IndexOutOfRangeException: Index was outside the bounds of the array.
    at Microsoft.Mashup.Engine1.Runtime.TableValue.Microsoft.Mashup.Engine.Interface.ITableValue.ColumnIdentity(Int32 index)
    at Microsoft.Mashup.Evaluator.ArrayHelpers.NewArray[T](Int32 count, Func`2 getter)
    at Microsoft.Mashup.Evaluator.ITableSourceSerializationExtensions.WriteITableSource(BinaryWriter writer, ITableSource tableSource)
    at Microsoft.Mashup.Evaluator.BinarySerializer.Serialize(Action`1 serializer)
    at Microsoft.Mashup.Evaluator.Interface.BufferedMessage.Prepare()
    at Microsoft.Mashup.Evaluator.ChannelMessenger.PostWithoutFlowControl(MessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass0_0.<RunStub>b__0()
    at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
    --- End of inner exception stack trace ---
    at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass11_0.<TryReportException>b__1()
    at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
    at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
    at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
    at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPreviewValueSource)
    at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass12_1`1.<OnBeginGetResult>b__0()
    at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
    at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
    at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
    at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
    at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
    at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
    at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
    at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
    at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
    --- End of inner exception stack trace ---
    at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
    at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
    at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
    at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
    at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_TableSource()
    at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_TableSource()
    at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

If I omit then SimilarityColumnName argument the matching works. Are there any solutions to get a column indicating the similarity evaluated?

Original M Query:

 let
        
     t1=
      let
     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSspPUorVAdNJlRBWJlQkMQnBjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
     #"Changed Type" = Table.TransformColumnTypes(Source,{
                 {"Column1", type text}}),
     #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
     in
      #"Added Index",
         
     t2=
         let
     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSspPUorVAdNJlWBWVmJeqgKMlZdaAmHmZ+QpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
     #"Changed Type" = Table.TransformColumnTypes(Source,{
                 {"Column1", type text}}),
     #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{
                 {"Column1", "ColumnMatch"}}),
     #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Indexy", 0, 1, Int64.Type)
     in
         #"Added Index",
        
     Source = Table.FuzzyJoin(t1, "Column1", t2, "ColumnMatch", 1, [ConcurrentRequests=null, Culture=null, IgnoreCase=null, IgnoreSpace=null, NumberOfMatches=5, SimilarityColumnName="sim", Threshold=0.8, TransformationTable=null])
 in
     Source

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

Hi @ErikBohn-1476

Can repro the issue with just that option on XL365 x64 v2105 b14026.20308:

 let
     t1 = Table.FromList({"bob","bobby","bib","bab","bib"}, null,
         type table [Column1=text]
     ),
     t2 = Table.FromList({"bob","bobby","janne","jannette","john"}, null,
         type table [cMatch=text]
     ),
     Source = Table.FuzzyJoin(
         t1,"Column1",
         t2,"cMatch",
         JoinKind.Inner,
         [SimilarityColumnName="ABC"]
     )
 in
     Source

I suggest you send this as a Frown and update your initial post with your product version + build (dev. team monitors the forum on a regular basis)

In the meantime it seems you can use Table.FuzzyNestedJoin. The following works here:

 ...
     Source = Table.FuzzyNestedJoin(
         t1,"Column1",
         t2,"cMatch",
         "Foo", JoinKind.Inner,
         [NumberOfMatches=5, SimilarityColumnName="ABC"]
     ),
     ExpandedFoo = Table.ExpandTableColumn(Source, "Foo", {"cMatch", "ABC"})

Hope this helps a bit

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.