Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago
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's avatar
Zubair_Muhammad
Community Champion
7 years ago

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





 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Replacements = [
                Int-A = "Beverages",

    Invalid Identifier occurs here.


    Expresion.SyntaxError: Invalid identifier

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      7 years ago

      Anonymous

       

      Try

      Change

       

      Int-A

      to

      #"Int-A"

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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

         

        And it ocurrs at

         

        Replaced=....

         

        in

         

        Replaced