Forum Discussion

DeepakNarayan's avatar
DeepakNarayan
Regular Visitor
6 years ago
Solved

How to rename column name as current Date dynamically?

Hi,

 

I want to rename a column name as current Date/current Year Month. Could anybody help me on this?

 

Thanks,

Deepak Narayan

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi DeepakNarayan 

     

    Try the below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUTJVitUhgmOElWMB5BgaEMuzQOPEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, #"Value " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Value ", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type", { 
            {
                "Year", 
                let 
                    currentDateTime = DateTimeZone.LocalNow(),
                    currentYear = Number.ToText( Date.Year( currentDateTime ) ),
                    currentMonth =  Date.MonthName( currentDateTime ) 

                in  currentYear & "-" & currentMonth 
            } 
        } 
    )
in
    #"Renamed Columns"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

6 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi DeepakNarayan 

     

    Try the below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUTJVitUhgmOElWMB5BgaEMuzQOPEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, #"Value " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Value ", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type", { 
            {
                "Year", 
                let 
                    currentDateTime = DateTimeZone.LocalNow(),
                    currentYear = Number.ToText( Date.Year( currentDateTime ) ),
                    currentMonth =  Date.MonthName( currentDateTime ) 

                in  currentYear & "-" & currentMonth 
            } 
        } 
    )
in
    #"Renamed Columns"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • GokilaRaviraj's avatar
    GokilaRaviraj
    Helper II
    1 year ago

    Hi 

    I Have a table with different columns , Sep-CFY, Oct-CFY and so till Dec-NFY.

    In this table i am showing current month + remaining months of the year + next year all the months. 

     

    How to rename this CFY and NFY dynamically with current and next years for all the columns. Or can this be done in visual dynamically ?

     

    Could any one pls help me with this ?

    • dufoq3's avatar
      dufoq3
      Community Champion
      1 year ago

      Hi GokilaRaviraj, check this:

       

      Before

       

      After

       

      v1

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslTSUTI0ABGGIMIIRAAxiDYGYhMgNgViMyA2B2ILIMbQEhsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sep CFY" = _t, #"Oct CFY" = _t, #"Nov CFY" = _t, #"Dec CFY" = _t, #"Jan NFY" = _t, #"Feb NFY" = _t, #"Mar NFY" = _t, #"Apr NFY" = _t, #"May NFY" = _t, #"Jun NFY" = _t, #"Jul NFY" = _t, #"Aug NFY" = _t, #"Sep NFY" = _t, #"Oct NFY" = _t, #"Nov NFY" = _t, #"Dec NFY" = _t]),
    CFY_NFY_ToYears = Table.TransformColumnNames(Source, each
        if Text.Contains(_, "CFY") then Text.Replace(_, "CFY", Text.From(Date.Year(DateTime.FixedLocalNow()))) else
        if Text.Contains(_, "NFY") then Text.Replace(_, "NFY", Text.From(Date.Year(DateTime.FixedLocalNow())+1))
        else _ )
in
    CFY_NFY_ToYears

       

       

      v2

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslTSUTI0ABGGIMIIRAAxiDYGYhMgNgViMyA2B2ILIMbQEhsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sep CFY" = _t, #"Oct CFY" = _t, #"Nov CFY" = _t, #"Dec CFY" = _t, #"Jan NFY" = _t, #"Feb NFY" = _t, #"Mar NFY" = _t, #"Apr NFY" = _t, #"May NFY" = _t, #"Jun NFY" = _t, #"Jul NFY" = _t, #"Aug NFY" = _t, #"Sep NFY" = _t, #"Oct NFY" = _t, #"Nov NFY" = _t, #"Dec NFY" = _t]),
    CFY_NFY_ToYears = Table.TransformColumnNames(Source, each 
        Text.Combine(List.ReplaceMatchingItems(
                        Text.Split(_, " "), 
                        { {"CFY", Text.From(Date.Year(DateTime.FixedLocalNow()))},
                          {"NFY", Text.From(Date.Year(DateTime.FixedLocalNow())+1)} } ), " ") )
in
    CFY_NFY_ToYears

       

  • GokilaRaviraj's avatar
    GokilaRaviraj
    Helper II
    1 year ago
     

    Hi dufoq3 ,

    Currently my input table looks like this,

    I am fetching current month + remaining months of the year+ next year all the months. This is the logic to fetch months every time.  These months I am fetching it from calendar table and appending here.

     

    In my original table, i have months till Dec_NFY.

     

    Now Estimate q1 value has to be copied to Where ever my table has Jan, Feb, Mar 

    Estimate q2 value has be copied to all the columns starts with Apr May jun 

    Same logic goes to Q3 and Q4 as well.

    Sample output.

    I am trying to do with this code.

    #"Replacevalue" = Table.ReplaceValue(#"Appended Query",
    each Text.StartsWith(_,"Jan") , each [Estimate Q1] , Replacer.ReplaceText, {"Jan"})

     

    This code is not working. 

    Could you pls help me with this issue?

     

    Thanks in advance.