Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
newcomerdev
New Member

Transforming Header Rows

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?

2 ACCEPTED SOLUTIONS
hackcrr
Super User
Super User

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.

View solution in original post

v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1715843800889.png

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.

 

View solution in original post

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1715843800889.png

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
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.