PowerQuery: Renaming fields

[Nusc]

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?

[Zubair_Muhammad · Accepted solution]

@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

[Nusc]

(Replacements, _, _)

What replaces the underscore?

[Zubair_Muhammad]

@Nusc

No need to replace underscore _ 

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

[Nusc]

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" ?

[Zubair_Muhammad]

@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

[Nusc]

Replacements = [
                Int-A = "Beverages",

Invalid Identifier occurs here.

Expresion.SyntaxError: Invalid identifier

[Zubair_Muhammad]

@Nusc

Try

Change

Int-A

to

#"Int-A"

[Nusc]

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

And it ocurrs at

Replaced=....

in

Replaced