Replace all in table not just a column

[WJ876400]

Hi,

I have a table of data from a survey and there are 6 questions that require a response of Strongly Agree, Agree, Neutral, Disagree and strongly agree. I want to add a number before each so I can sort the visuals. Is there a way to replace a value for all columns or will i have to do it individually for all columns.

So I want to replace all strongly agree with 1. Strongly Agree but dont want to have to do it by each column can I just do a replace all that finds strongly agree and then replace with 1. Strongly agree.

thanks in advance

[WJ876400]

Thank you very much

[bhanu_gautam · Accepted solution]

@WJ876400 , Try using below Power Query for this

let

    Source = YourDataSource,

    ReplaceValues = Table.TransformColumns(Source, List.Transform(Table.ColumnNames(Source), each {_, each Text.Replace(_, "Strongly Agree", "1. Strongly Agree"), type text})),

    ReplaceValues2 = Table.TransformColumns(ReplaceValues, List.Transform(Table.ColumnNames(ReplaceValues), each {_, each Text.Replace(_, "Agree", "2. Agree"), type text})),

    ReplaceValues3 = Table.TransformColumns(ReplaceValues2, List.Transform(Table.ColumnNames(ReplaceValues2), each {_, each Text.Replace(_, "Neutral", "3. Neutral"), type text})),

    ReplaceValues4 = Table.TransformColumns(ReplaceValues3, List.Transform(Table.ColumnNames(ReplaceValues3), each {_, each Text.Replace(_, "Disagree", "4. Disagree"), type text})),

    ReplaceValues5 = Table.TransformColumns(ReplaceValues4, List.Transform(Table.ColumnNames(ReplaceValues4), each {_, each Text.Replace(_, "Strongly Disagree", "5. Strongly Disagree"), type text}))

in

    ReplaceValues5

replace yourdatasource with actual source