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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.