Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
Solved! Go to 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.
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.
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
84 | |
76 | |
49 |
User | Count |
---|---|
143 | |
141 | |
109 | |
69 | |
55 |