Forum Discussion
Create a column using M language
Hello, I'm trying to create a column in the power query using the M language. The column that I'm trying to create is New Code column. So I want to add TT at the beginning and then the value that in column Name and then 000 and then the sequence. But I want the sequence to change once the name change as it shows in the exmple below. I also attached the formula that I used in Excel to create New Code column. ="TT"&A2&IFS(COUNTIF(A$2:A2,A2)<=9,"000"&COUNTIF(A$2:A2,A2),COUNTIF(A$2:A2,A2)<=99,"00"&COUNTIF(A$2:A2,A2),COUNTIF(A$2:A2,A2)<=999,"0"&COUNTIF(A$2:A2,A2),COUNTIF(A$2:A2,A2)<=9999,COUNTIF(A$2:A2,A2))
| Name | New Code |
| William | TTWilliam0001 |
| William | TTWilliam0002 |
| William | TTWilliam0003 |
| William | TTWilliam0004 |
| William | TTWilliam0005 |
| Thomas | TTThomas0001 |
| Thomas | TTThomas0002 |
| Thomas | TTThomas0003 |
| Thomas | TTThomas0004 |
| Thomas | TTThomas0005 |
| Daniel | TTDaniel0001 |
| Daniel | TTDaniel0002 |
| Daniel | TTDaniel0003 |
Hi Elmoataz ,
You need to group the table by Name and the grouping should be all rows. That will create a column of tables filtered by Name. Then an index column to those tables. Here's a sample M script.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/MyclMzFXSUVIICYFyDAwMDJVidXBKGuGTNMYnaYJP0hQsGZKRn5tYDJGDsOHOwSplhFvKGLeUCW4piDNcEvMyU3MgUhA23BlYpYxwSwGdEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"New Code" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"New Code", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Grouped", each _, type table [Name=nullable text, New Code=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Grouped], "Index", 1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Index"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "NewCode", each let _index = Text.PadStart( Text.From([Index]), 3, "0") in "TT" & [Name] & _index, type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Grouped", "Index"}) in #"Removed Columns"
2 Replies
Hi Elmoataz ,
You need to group the table by Name and the grouping should be all rows. That will create a column of tables filtered by Name. Then an index column to those tables. Here's a sample M script.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/MyclMzFXSUVIICYFyDAwMDJVidXBKGuGTNMYnaYJP0hQsGZKRn5tYDJGDsOHOwSplhFvKGLeUCW4piDNcEvMyU3MgUhA23BlYpYxwSwGdEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"New Code" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"New Code", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Grouped", each _, type table [Name=nullable text, New Code=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Grouped], "Index", 1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Index"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "NewCode", each let _index = Text.PadStart( Text.From([Index]), 3, "0") in "TT" & [Name] & _index, type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Grouped", "Index"}) in #"Removed Columns"
