Transforming Header Rows

[newcomerdev]

Hi,

I have about 40 columns of data, the header rows have for example 1_Fleet 2_Fleet but the 1 should reflect as a month i.e Jan_Fleet, Feb_Fleet for about 24 of these columns. How would I transform this without having to rename the column one by one?

[hackcrr · Accepted solution]

Hi, @newcomerdev 

You can use Power Query (M language) to implement this conversion in Power BI by renaming multiple columns based on the schema instead of manually renaming each column. You can do this:
Identify the columns that need to be renamed. In your case, columns with names such as 1_Fleet, 2_Fleet, etc.
Create a custom function to rename the column:
In the Power Query editor, go to the Home tab and click Advanced Editor. copy and paste the following M code into the editor, modifying it as needed to match your data:

let
    // Replace "YourTableName" with the actual name of your table
    Source = YourTableName,

    // Create a list of month abbreviations
    MonthNames = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"},

    // Generate a list of new column names based on the month and existing suffix
    RenameColumnsList = List.Transform(
        List.Zip({List.Transform({1..12}, each Text.From(_)), MonthNames}),
        each {_1 & "_Fleet", _2 & "_Fleet"}
    ),

    // Rename the columns
    RenamedColumns = Table.RenameColumns(Source, RenameColumnsList, MissingField.Ignore)
in
    RenamedColumns

If your original columns were named 1_Fleet, 2_Fleet, ... , 12_Fleet, the script above will rename them to Jan_Fleet, Feb_Fleet, ... , Dec_Fleet. By using this method, you can efficiently batch rename columns based on a pattern without having to do this manually for each column. If you have more columns than 12 months to rename, you can expand the list MonthNames and adjust the range {1..12} accordingly.

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xinruzhu-msft · Accepted solution]

Hi,

Thans for the solution @hackcrr  provided, and i want to offer some more infotmation for user to refer to.

hello @newcomerdev , you can create a blank query and input the following code to advanced editor in power query.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WSlTSQcOxOtFKSUAWKgaJJgNZqBgkmgJkoeLYWAA=", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        #"1_Fleet" = _t, 
        #"2_Fleet" = _t, 
        #"3_Fleet" = _t, 
        #"4_Fleet" = _t, 
        #"10_Fleet" = _t
      ]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"1_Fleet", type text}, 
      {"2_Fleet", type text}, 
      {"3_Fleet", type text}, 
      {"4_Fleet", type text}, 
      {"10_Fleet", type text}
    }
  ), 
  step1 = 
    let
      _tablecolumnname = Table.ColumnNames(#"Changed Type"), 
      //get the colum name
      _trans = List.Transform(_tablecolumnname, each Number.From(Text.Select(_, {"0" .. "9"}))), 
      //extract the number from the columnname
      _trans2 = List.Transform(_trans, each Date.ToText(#date(2024, _, 1), "MMM") & "_" & "Fleet"), 
      //change the number to monthname
      _zip = List.Zip({_tablecolumnname, _trans2})
    //combine the original columnname and the new name
  in  _zip, 
  step2 = Table.RenameColumns(#"Changed Type", step1)
//rename the columnname
in step2

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hackcrr · Accepted solution]

Hi, @newcomerdev 

You can use Power Query (M language) to implement this conversion in Power BI by renaming multiple columns based on the schema instead of manually renaming each column. You can do this:
Identify the columns that need to be renamed. In your case, columns with names such as 1_Fleet, 2_Fleet, etc.
Create a custom function to rename the column:
In the Power Query editor, go to the Home tab and click Advanced Editor. copy and paste the following M code into the editor, modifying it as needed to match your data:

let
    // Replace "YourTableName" with the actual name of your table
    Source = YourTableName,

    // Create a list of month abbreviations
    MonthNames = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"},

    // Generate a list of new column names based on the month and existing suffix
    RenameColumnsList = List.Transform(
        List.Zip({List.Transform({1..12}, each Text.From(_)), MonthNames}),
        each {_1 & "_Fleet", _2 & "_Fleet"}
    ),

    // Rename the columns
    RenamedColumns = Table.RenameColumns(Source, RenameColumnsList, MissingField.Ignore)
in
    RenamedColumns

If your original columns were named 1_Fleet, 2_Fleet, ... , 12_Fleet, the script above will rename them to Jan_Fleet, Feb_Fleet, ... , Dec_Fleet. By using this method, you can efficiently batch rename columns based on a pattern without having to do this manually for each column. If you have more columns than 12 months to rename, you can expand the list MonthNames and adjust the range {1..12} accordingly.

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.