Forum Discussion
PowerQuery: Renaming fields
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 manu...
Anonymous
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
Anonymous
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
Anonymous
Try
ChangeInt-A
to
#"Int-A"
This worked but now it says, "Token Comma Expected"
And it ocurrs at
Replaced=....
in
Replaced