Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Change column name to use other column value

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 CMd...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.