Conditional match to the reference table in Excel Power Query

[L70F]

Hi, 

I need help with M-codes to Excel Power Query.

I have a list of items with com.codes and reference table with responsible people for different com.groups.

I would like to have the result, where if com.code starts with the value of com.group in the reference table, the responsible person's name would be retrieved.

Item list:

Iten no	Com.code
TE40S520	310
901628	455
AG63201	520
590328	460
223406	*
223422	000
590327	610

Reference table:

Com.group	Responsible
3	Anna
5	Peter
4	Alex
45	Anna
46	Anna
6	Anders
*	Wrong com.code
0	Wrong com.code

Result table:

Iten no	Com.code	Responsible
TE40S520	310	Anna
901628	455	Anna
AG63201	520	Peter
590328	460	Anna
590327	610	Anders
223406	*	Wrong com.code
223422	000	Wrong com.code

Thanks a lot

Best regards

Larissa

[Anonymous]

A bit strange rules, as some comgroups are contained in others. I mean that in case the start of a com.code is in different com.groups I take the comgroup manager which has a longer overlap.

item:

let
let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc0xDoAwDAPAv2REHVynCXRkQDwAtqr//wYplRCjT7bcmtxHwWWEJNEM6alJRXZuAcXshf10JXLIKA6xCp0Vn0BqgQcsXyQjAr/BGuDjpD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Iten no" = _t, Com.code = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Iten no", type text}, {"Com.code", type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "resp", each Table.SelectRows(responsS, (r)=> 
Text.StartsWith(r[Com.group], Text.Start([Com.code],1))){0}[Responsible])
in
    #"Aggiunta colonna personalizzata"

responsS:

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUXLMy0tUitWJVjIFcgJSS1KLwDwTkFROagWEY4qs0MQMmQfhpKQWFYO5WkBueFF+XrpCcn6uXnJ+SipY2ACLcCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Com.group = _t, Responsible = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Com.group", type text}, {"Responsible", type text}}),
    #"Merge di colonne" = Table.CombineColumns(#"Modificato tipo",{"Com.group", "Responsible"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"merge"),
    ls=List.Sort(#"Merge di colonne"[merge], {(x)=>Text.Length(Text.BeforeDelimiter(x,"=")), Order.Descending}),
    #"Conversione in tabella" = Table.FromList(ls, Splitter.SplitTextByDelimiter("="), null, null, ExtraValues.Error),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Conversione in tabella",{{"Column1", type text}, {"Column2", type text}}),
    #"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo1",{{"Column1", "Com.group"}, {"Column2", "Responsible"}})
in
    #"Rinominate colonne"

[Anonymous · Accepted solution]

this solution requires no changes to the item table

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc0xDoAwDAPAv2REHVynCXRkQDwAtqr//wYplRCjT7bcmtxHwWWEJNEM6alJRXZuAcXshf10JXLIKA6xCp0Vn0BqgQcsXyQjAr/BGuDjpD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Iten no" = _t, Com.code = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Iten no", type text}, {"Com.code", type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "resp", each let l= Table.SelectRows(respons, (r)=> 
          Text.StartsWith(r[Com.group], Text.Start([Com.code],1)))[Responsible], sp = if List.Count(l)=1 then l{0} else Table.SelectRows(respons, (r)=> 
          Text.StartsWith(r[Com.group], Text.Start([Com.code],2)))[Responsible]{0} in sp)
in
    #"Aggiunta colonna personalizzata"

[L70F]

Thank you, Rocco. Nice solution. 😊 It works well.