Dynamic field name

[DanCasSan]

Dear community, I need your important support. Is it possible to configure the name of a field to vary according to certain situations? For example, that my "Amounts" field is named "April Amounts" when I place myself in that month, as now, but that it changes to "May Amounts" for the following month.

It should be noted that my report does not contain a date filter, therefore, it has different fields with the last 4 months. I leave you an image of my fields.

Regards,

[Anonymous]

My long detailed and screenshotted message was marked as spam for some reason. Until I get a chance to recover the missing images, here is a short version of my original reply.

Here is a link to a sample report containing the code described here with which you can play.

Go to Power Query M Function Reference, and find the functions you need to create the string you want. In the case of the current month column it is this:

Text.Combine({Date.MonthName(DateTime.LocalNow()),Number.ToText(Date.Year(DateTime.LocalNow()))}," ")}​

In the case of the next month column, invoke the Date.AddMonths() function, as shown here:

= Table.RenameColumns(#"Renamed Columns",{{"Movies Next Month",Text.Combine({Date.MonthName(Date.AddMonths(DateTime.LocalNow(),1)),Number.ToText(Date.Year(DateTime.LocalNow()))}," ")}})

Finally, to get the name of the prior month,  use -1 instead of 1 when calling Date.AddMonths(), as shown here:
 

= Table.RenameColumns(#"Renamed Columns",{{"Movies Last Month",Text.Combine({Date.MonthName(Date.AddMonths(DateTime.LocalNow(),-1)),Number.ToText(Date.Year(DateTime.LocalNow()))}," ")}})

If this solution answers your question, please take the time to mark this as a solution. I think it is a good question so I hope others will benefit from this solution as well.

[Anonymous · Accepted solution]

Interesting question. If you only care about generating a data set and there will be no visualizations using the dynamically-named fields, then, yes, you can dynamically rename columns in the query.

In a hurry? Here is a link to a sample report containing the code described here with which you can play and, this following code block is the simple self-contained example in which three columns labeled "This Month", "Last Month", and "Next Month" are dynamically renamed to their actual values. Just create a blank query and drop this in.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lEyNgYSRgZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Movies This Month" = _t, #"Movies Last Month" = _t, #"Movies Next Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Movies This Month", Int64.Type}, {"Movies Last Month", Int64.Type}, {"Movies Next Month", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Movies This Month",Text.Combine({Date.MonthName(DateTime.LocalNow()),Number.ToText(Date.Year(DateTime.LocalNow()))}," ")}}),
    Custom1 = Table.RenameColumns(#"Renamed Columns",{{"Movies Last Month",Text.Combine({Date.MonthName(Date.AddMonths(DateTime.LocalNow(),-1)),Number.ToText(Date.Year(DateTime.LocalNow()))}," ")}}),
    Custom2 = Table.RenameColumns(#"Custom1",{{"Movies Next Month",Text.Combine({Date.MonthName(Date.AddMonths(DateTime.LocalNow(),1)),Number.ToText(Date.Year(DateTime.LocalNow()))}," ")}})
in
    Custom2

An easier way to start when you don't know where to start is to manually rename the column to anything in the query editor. I know. You don't want to do this manually but by doing so, you'll see the code generated which you can then modify to do what you want.

Here is what you do:

Start with the query which returns the table whose column names you want to set dynamically.

The table I'm showing only has one row. This doesn't matter. The following steps will work regardless of the number of rows.

At the time I'm writing this it is April of 2020. I want to rename the column labeled "Movies This Month" to "April 2020". If you don't know how to use the Advanced Editor or even where to start on the statement to rename a column, you should just do the closest command. In this case it is to manually rename the column by double-clicking on the column name and replacing it with the name you want. This image shows me entering the beginning of the new column name, "Apri".

When finished, a new step will have been applied, which, as you can see on the right, is labeled Renamed Columns.

In the formula bar you see the statement generated by your manually renaming the column is this:

= Table.RenameColumns(#"Changed Type",{{"Movies This Month", "April 2020"}})

If you don't see the formula bar above the table as shown in this image, click on View in the top menu and check the box labeled Formula Bar.

I won't go into detail explaining the complete statement at this time. What I want you to notice, though, is how the new name is clearly visible in the formula, "April 2020". This tells me I can probably replace that with a function, much as I may do in Excel, for example.

I go to Power Query M Function Reference, and find the functions I need to create the string I want. In this case of the current month column it is this:

Text.Combine({Date.MonthName(DateTime.LocalNow()),Number.ToText(Date.Year(DateTime.LocalNow()))}," ")}​

In the case of the next month column, I invoke the Date.AddMonths() function, as shown here:

= Table.RenameColumns(#"Renamed Columns",{{"Movies Next Month",Text.Combine({Date.MonthName(Date.AddMonths(DateTime.LocalNow(),1)),Number.ToText(Date.Year(DateTime.LocalNow()))}," ")}})

Finally, to get the name of the prior month, I use -1 instead of 1 when I call Date.AddMonths(), as shown here:
 

= Table.RenameColumns(#"Renamed Columns",{{"Movies Last Month",Text.Combine({Date.MonthName(Date.AddMonths(DateTime.LocalNow(),-1)),Number.ToText(Date.Year(DateTime.LocalNow()))}," ")}})

When I'm done, this is what I see: 

If this solution answers your question, please take the time to mark this as a solution. I think it is a good question so I hope others will benefit from this solution as well.

[v-yiruan-msft]

Hi @DanCasSan ,

The data in original field PRDLIT_0,PRDLIT_1,PRDLIT_2 and PRDLIT_3 are updated dynamically? For example, if the current month is August, then the field name need to display as "Acum August ","Acum July","Acum June" and "Acum May"? Could you please provide some sample data (exclude sensitive data) and your expected data with the screen shot? Thank you.

In addition, I'm not sure whether the solution in the following threads are applicable for your scenarios. Please check.

https://community.powerbi.com/t5/Power-Query/dynamic-column-name-in-formula/m-p/962682

https://community.powerbi.com/t5/Desktop/Dynamic-column-name-from-its-value/m-p/189442

https://community.powerbi.com/t5/Power-Query/Dynamically-update-column-names/m-p/706856

Best Regards

Rena

[DanCasSan]

Hi @v-yiruan-msft.

Exactly, the data you mention is already updated with the correct information per month, all I want is to change the name of the field, that is, change the names of PRDLIT_0 to "Acum Abril", PRDLIT_1 to "Acum Marzo", PRDLIT_2 to "Accum February" and PRDLIT_3 for "Accum January".

Regards,

[v-yiruan-msft]

Hi @DanCasSan ，

I'm not sure if the following one is what you want...

1. Select the field which you want to rename

2. Click the option "Rename" and type the new name

Best Regards

Rena

[DanCasSan]

@v-yiruan-msft hi, this must be automatic, not manual; since it must be changed depending on the variability of the months.

[v-yiruan-msft]

Hi @DanCasSan ,

I'm so sorry I have yet to find a suitable method to achieve this...

Best Regards

Rena