Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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?
Solved! Go to Solution.
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
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?
No need to replace underscore _
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" ?
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
Try
Change
Int-A
to
#"Int-A"
This worked but now it says, "Token Comma Expected"
And it ocurrs at
Replaced=....
in
Replaced
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
66 | |
61 | |
46 | |
45 |