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"