Forum Discussion
Solved
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 ...
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"
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"
