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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Nusc
Helper II
Helper II

PowerQuery: Renaming fields

omg2.JPG

 

In column B you see that there is Beer, Pop and Water. In PowerQuery, how do I change this to a new value called "Beverages" ?

 

I filtered for Beer, Pop, Water and replaced the value manually with Beverages but I can't unfilter the result to retrieve the other values. What is the proper way to do this?

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@Nusc

 

Here is one way of doing it

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replacements = [
        Beer = "Beverages",
        Pop = "Beverages",
        Water = "Beverages"],
    Replaced = Table.TransformColumns(Source, {{"Category", each Record.FieldOrDefault(Replacements, _, _)}})
in
    Replaced

View solution in original post

8 REPLIES 8
Zubair_Muhammad
Community Champion
Community Champion

@Nusc

 

Here is one way of doing it

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replacements = [
        Beer = "Beverages",
        Pop = "Beverages",
        Water = "Beverages"],
    Replaced = Table.TransformColumns(Source, {{"Category", each Record.FieldOrDefault(Replacements, _, _)}})
in
    Replaced

(Replacements, _, _)

What replaces the underscore?

@Nusc

 

No need to replace underscore _  Smiley Wink

See the attached Excel file with your sample data and Power Query renaming

let
       Source = Excel.Workbook(File.Contents("C:\...filename.xlsx"), null, true), sheet = Source{[Item]-"sheet", Kind="Sheet"]}[Data],
#"Promoted Headers"= Table.PromoteHeaders(sheet,[PromoteAllScalars=true]),
#"CHanged Type" = TableTransformColumnTypes(#"Promoted Headers",{{"Time", type any},{"Last Name", type text},{"Case type", type text",{"Date", type text}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type", 6),
Replacements = [
                Int-A = "Beverages",
                Int-B = "Beverages",
                int-C= "Beverages",
               Replaced = TableTransformColunns(Source,{{...same as yours}}
],
In
#"Removed Bottom Rows"
Replaced = Table TransformColumns(

The error I receive is that the first entry after

Replacements = [

Int-A....

 

Shows up as an invalid identifier, what does this mean?

 

When I posted the example, I did not include the previous operations I performed before it. My concern is does the number of replacements affect the "Replaced =" line ?

In the "in" section, is there a comma needed after "Removed bottom rows" ?

@Nusc

 

Sorry for late reply. I had to go out.

 

Please send me your file I will fix it for you

 

All the code needs to be before "IN"

Try this. Just Replace "Category" with your Column Name where replacements need to be made

let
       Source = Excel.Workbook(File.Contents("C:\...filename.xlsx"), null, true), sheet = Source{[Item]-"sheet", Kind="Sheet"]}[Data],
#"Promoted Headers"= Table.PromoteHeaders(sheet,[PromoteAllScalars=true]),
#"CHanged Type" = TableTransformColumnTypes(#"Promoted Headers",{{"Time", type any},{"Last Name", type text},{"Case type", type text",{"Date", type text}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type", 6),
Replacements = [
                Int-A = "Beverages",
                Int-B = "Beverages",
                int-C= "Beverages"],
Replaced = Table.TransformColumns(#"Removed Bottom Rows", {{"Category", each Record.FieldOrDefault(Replacements, _, _)}})

in
Replaced





 

 

Replacements = [
                Int-A = "Beverages",

Invalid Identifier occurs here.


Expresion.SyntaxError: Invalid identifier

This worked but now it says, "Token Comma Expected"

 

And it ocurrs at

 

Replaced=....

 

in

 

Replaced

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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