How to rename column name as current Date dynamically?

[DeepakNarayan]

Hi,

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

Thanks,

Deepak Narayan

[GokilaRaviraj]

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.

[GokilaRaviraj]

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]

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]

Hi @dufoq3 

It worked Thank you 🙂

[dufoq3]

You're welcome.

[Mariusz · Accepted solution]

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.