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, whe...
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"
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.
