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 Sept...
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

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