Forum Discussion

GokilaRaviraj's avatar
GokilaRaviraj
Helper II
1 year ago
Solved

Copy and paste entire column value into another column based on current month or not

Hi 

 

Can anyone please tell me how to copy entire column data and paste that values in another empty column based on current month name.

This is how my table looks now. Since we are in September month, Other income value has to be copied in September and other column values i need to enter manual data. 

I have fetched current month + 11 months dynamically and appended those to this table where in im trying to copy and paste.

 

Please give me some solution.

Need Help
Tips and Tricks
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    GokilaRaviraj , Try using below m-code

     

    let
    // Load your data source
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],

    // Add a column with the current month name
    AddCurrentMonth = Table.AddColumn(Source, "CurrentMonth", each Date.ToText(DateTime.LocalNow(), "MMMM"), type text),

    // Duplicate the "Other income" column
    DuplicateOtherIncome = Table.AddColumn(AddCurrentMonth, "CurrentMonthData", each [Other income]),

    // Add a conditional column to copy data based on the current month
    AddConditionalColumn = Table.AddColumn(DuplicateOtherIncome, "UpdatedColumn", each if [CurrentMonth] = "September" then [CurrentMonthData] else null),

    // Remove unnecessary columns
    RemoveColumns = Table.RemoveColumns(AddConditionalColumn,{"CurrentMonth", "CurrentMonthData"}),

    // Rename the updated column to the current month name
    RenameColumn = Table.RenameColumns(RemoveColumns, {{"UpdatedColumn", Date.ToText(DateTime.LocalNow(), "MMMM")}})
    in
    RenameColumn

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    GokilaRaviraj , Try using below m-code

     

    let
    // Load your data source
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],

    // Add a column with the current month name
    AddCurrentMonth = Table.AddColumn(Source, "CurrentMonth", each Date.ToText(DateTime.LocalNow(), "MMMM"), type text),

    // Duplicate the "Other income" column
    DuplicateOtherIncome = Table.AddColumn(AddCurrentMonth, "CurrentMonthData", each [Other income]),

    // Add a conditional column to copy data based on the current month
    AddConditionalColumn = Table.AddColumn(DuplicateOtherIncome, "UpdatedColumn", each if [CurrentMonth] = "September" then [CurrentMonthData] else null),

    // Remove unnecessary columns
    RemoveColumns = Table.RemoveColumns(AddConditionalColumn,{"CurrentMonth", "CurrentMonthData"}),

    // Rename the updated column to the current month name
    RenameColumn = Table.RenameColumns(RemoveColumns, {{"UpdatedColumn", Date.ToText(DateTime.LocalNow(), "MMMM")}})
    in
    RenameColumn

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    Hi GokilaRaviraj, check this:

     

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0ABJQFKsTrZQEZBihiSUDGcbIYrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, #"Other Income" = _t, september = _t, october = _t, november = _t]),
    // You can probably delete this step with your data.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    ChangedType = Table.TransformColumnTypes(ReplaceBlankToNull,{{"Other Income", type number}}),
    ReplacedCurrentMonth = Table.ReplaceValue(ChangedType,
        each null,
        each [Other Income],
        Replacer.ReplaceValue,
        {Text.Lower(DateTime.ToText(DateTime.FixedLocalNow(), "MMMM", "en-US"))} )
in
    ReplacedCurrentMonth