Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Replace the first value of a specific column.

I have a column named Reports. How can I replace only the first value in this column with "bananas" for example?

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    Anonymous , Use below m code for this

    let
    Source = ... , // Your data source here
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    ReplaceFirstValue = Table.AddColumn(AddIndex, "Updated Reports", each if [Index] = 0 then "bananas" else [Reports]),
    RemoveIndex = Table.RemoveColumns(ReplaceFirstValue,{"Index"}),
    FinalTable = Table.RemoveColumns(RemoveIndex,{"Reports"}),
    RenamedTable = Table.RenameColumns(FinalTable,{{"Updated Reports", "Reports"}})
    in
    RenamedTable

     

6 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago
    Table.ReplaceRows(Source, 0, 1, {Table.First(Source) & [Reports = "banana"]})
  • Omid_Motamedise's avatar
    Omid_Motamedise
    Icon for Super User rankSuper User
    2 years ago

    See this small example 

    let
    Source = Table.FromRecords({
    [Reports = 1],
    [Reports = 2],
    [Reports = 3]
    }),
    Custom1 = Table.FromRecords({Source{0} & [Reports="Banana"]}) & Table.RemoveFirstN(Source,1)
    in
    Custom1

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Anonymous , Use below m code for this

    let
    Source = ... , // Your data source here
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    ReplaceFirstValue = Table.AddColumn(AddIndex, "Updated Reports", each if [Index] = 0 then "bananas" else [Reports]),
    RemoveIndex = Table.RemoveColumns(ReplaceFirstValue,{"Index"}),
    FinalTable = Table.RemoveColumns(RemoveIndex,{"Reports"}),
    RenamedTable = Table.RenameColumns(FinalTable,{{"Updated Reports", "Reports"}})
    in
    RenamedTable

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I tried using this:
      ReplaceLineValue = Table.TransformColumns( AddIndex, {{"Line", each if [Index] = 0 or List.Contains(List.Transform(MachineNamePositions, each _ + 2), [Index]) then "Line" else _}})
      I want to replace when the Index is 0 and when the index is equal to machineNamePositions plus 2 rows. Machine name positions is a list of indexes.
      MachineNamePositions = Table.SelectRows(AddIndex, each [Column1] = "Machine")[Index]

      But this didnt replace the values.

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    2 years ago

    Try this:

    let
    Source = Table.FromRecords(
        {[Column1 = "A"],
        [Column1 = "B"],
        [Column1 = "C"]},
        type table [Column1=text]),
    
    Banana = Table.FromColumns(
        {{"Banana"} & List.RemoveFirstN(Source[Column1])},
        type table[Column1=text])
in
    Banana
  • p45cal's avatar
    p45cal
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Look at the ReplacedValue step.

    Put the code below into a blank Advanced Editor.

     

    let
    Source = Table.ExpandRecordColumn(Table.FromList(Json.Document(Binary.FromText( "W3siSGRyMSI6InN0dWZmIiwiUmVwb3J0cyI6Im9uZSIsIkhkcjMiOiJzdHVmZiJ9LHsiSGRyMSI6InN0dWZmIiwiUmVwb3J0cyI6InR3byIsIkhkcjMiOiJzdHVmZiJ9LHsiSGRyMSI6InN0dWZmIiwiUmVwb3J0cyI6InRocmVlIiwiSGRyMyI6InN0dWZmIn0seyJIZHIxIjoic3R1ZmYiLCJSZXBvcnRzIjoiZm91ciIsIkhkcjMiOiJzdHVmZiJ9LHsiSGRyMSI6InN0dWZmIiwiUmVwb3J0cyI6ImZ2ZSIsIkhkcjMiOiJzdHVmZiJ9XQ==",BinaryEncoding.Base64),1252), Splitter.SplitByNothing()), "Column1", {"Hdr1", "Reports", "Hdr3"}),
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    ReplacedValue = Table.ReplaceValue(AddedIndex,each [Reports],each if [Index] = 0 then "banana" else [Reports],Replacer.ReplaceValue  ,{"Reports"}),
    RemovedColumns = Table.RemoveColumns(ReplacedValue,{"Index"})
in
    RemovedColumns