Trying to replace values in one column efficiently

John Hoecker 1 Reputation point
2021-06-17T19:13:42.057+00:00

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!

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,807 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Lz._ 8,986 Reputation points
    2021-06-17T20:38:01.173+00:00

    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