Change column name to use other column value

[Anonymous]

Hi All,

I want to change the column name and use other column values

The data table is shown below, the name of column SoldQtyLM uses LMdate value 202102, and the name of column SoldQtyMTD uses CMdate value 202103.

How should I modify it. Thank you.

[Anonymous]

Hi @Anonymous ,

1. Do you want to keep all these 6 columns and replace the value of column SoldQtyLM, SoldQtyMTD and SoldQtyCD with the value of column LMdata, CMdate and CDate separately? If yes, you can achieve it using function Table.ReplaceValue. Please copy and paste the following codes into your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQwUtKBMIzhDANzINMAjmN1RpXSRGksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LMdate = _t, CMdate = _t, CDate = _t, SoldQtyLM = _t, SoldQtyMTD = _t, SoldQtyCD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LMdate", Int64.Type}, {"CMdate", Int64.Type}, {"CDate", Int64.Type}, {"SoldQtyLM", Int64.Type}, {"SoldQtyMTD", Int64.Type}, {"SoldQtyCD", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",0,each [LMdate],Replacer.ReplaceValue,{"SoldQtyLM"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",0,each [CMdate],Replacer.ReplaceValue,{"SoldQtyMTD"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",0,each [CDate],Replacer.ReplaceValue,{"SoldQtyCD"})
in
    #"Replaced Value2"

2. If you want to only keep the column SoldQtyLM, SoldQtyMTD and SoldQtyCD with the values from the values of column LMdate, CMdata and Cdata, you can remove the column SoldQtyLM, SoldQtyMTD and SoldQtyCD first and rename the column LMdate, CMdata and Cdata as SoldQtyLM, SoldQtyMTD and SoldQtyCD just like below screenshot.

Best Regards

[Anonymous]

Hi Rena,

Thank you for your reply. I'm sorry maybe I didn't speak clearly enough. Just want to replace the Column name so that it can be displayed on the report. Thanks.

Best Regards,

twong

[Anonymous · Accepted solution]

Hi @Anonymous ,

Sorry that I misunderstood your requirement. Please copy and paste the updated codes into your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQwUtKBMIzhDANzINMAjmN1RpXSRGksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LMdate = _t, CMdate = _t, CDate = _t, SoldQtyLM = _t, SoldQtyMTD = _t, SoldQtyCD = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"SoldQtyLM",Source[LMdate]{0}},{"SoldQtyMTD",Source[CMdate]{0}},{"SoldQtyCD",Source[CDate]{0}}})
in
    #"Renamed Columns"

Best Regards

[Anonymous]

Hi @Anonymous 

Thanks for the quick response.

But there is still a issue. When I release a report for a new day, the date in the column name will change, and the report cannot recognize the column. How should I solve it?

Thank you once again.

[Anonymous]

Hi @Anonymous ,

Did you applied any other step after the step "Renamed Columns"? If yes, it may cause the problem. Please try to apply the other steps first and put the step "Renamed Columns" as the last step....

Best Regards

[Anonymous]

Hi @Anonymous 

The following is the report layout. When the second day is updated, due to the date conversion, the report cannot see the new day and an error occurs. Thanks.

Regards,

twong

[Anonymous]

Hi @Anonymous ,

I'm afraid that there is no elegant method to fix this problem except delete the field with error and add the "new" field back to the visual. The field with old name is used in visual, it is static. And it can't update the field in the visual by sync after rename the field. You can raise a new idea and add your comments there to make this feature coming sooner. 

Best Regards