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
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
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
    (Replacements, _, _)

    What replaces the underscore?

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