Forum Discussion
Solved
Conditional match to the reference table in Excel Power Query
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
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"
3 Replies
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"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"Thank you, Rocco. Nice solution. 😊 It works well.
