Trying to replace values in one column efficiently

Question

Hi folks.

I'm a newb trying to clean up my data in an efficient way in power query. All I'm trying to do is replace one value with another in the "Category"
column.

let
    Source = Csv.Document(File.Contents("C:\Users\JOHN\OneDrive\Documents\PBIX Files\Original data\Financials.csv"),[Delimiter=",", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers1" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])

    in

    #"Replacer.ReplaceValue" = Table.FromRecords(Table.TransformRows(Source, 
(row) as record => 
  if row[Category]="Household" then 
    Record.TransformFields(row,{<!-- -->{"Category", each "Household"},
                                {"Category", each "Utility"}}) 
  else 
    (row)
in

I suspect its something fundamental - the error message is a token comma around the final "in", but no success.

This is my workaround for the problem of trying to replace a value in one column based on the value in another column - was that ever solved?

Thank you!

Answer 1

Hi @John Hoecker

I didn't look in details to your query so not sure it will deliver what you expect. However, to make it "acceptable" from a syntax perspective it should like this:

let  
    Source = Csv.Document(...),  
    #"Promoted Headers1" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),        
    #"Replacer.ReplaceValue" = Table.FromRecords(  
        Table.TransformRows(#"Promoted Headers1",   
            (row) as record =>   
                if row[Category] = "Household"  
                then  
                    Record.TransformFields(row,  
                        {<!-- -->{"Category", each "Household"}, {"Category", each "Utility"}}  
                    )   
            else row  
        )  
    )  
in  
    #"Replacer.ReplaceValue"  

PS Please update the title of your thread so it better reflects the issue. Thanks