Create a column using M language

[Elmoataz]

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

[danextian · Accepted solution]

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"

[Elmoataz]

Thanks @danextian. I also have another question. Can I use the M script for Sharepoint? Will it work or it's different from power BI.