Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |