locked
How to add a new column formula that modifies content RRS feed

  • Question

  • Hello. I have a column with numbers as text, and the problematic one is that there is a ">300"

    How can I write a formula in power query that modifies all the numbers to text and replaces all instances of ">300" with the number 0? 

    Friday, January 24, 2020 1:43 AM

Answers

  • Oh yes! I figured it out. I just needed to use a conditional column that replaced the value and then change data type to whole number. Boom!
    • Marked as answer by Albo44 Friday, January 24, 2020 5:31 AM
    Friday, January 24, 2020 5:31 AM

All replies

  • Hi

    You already asked a number of questions in a few days so I wonder if you already know..

    • Power Query documentation is here
    • A page with some learning resources is here
    • This site already contains a ton of solutions that can be found using the Search box

    Also, when you Search and something helps you, there's a Vote "button" you can use… Finally, when you get a proposal that answers your question/solves your problem there's a Mark as answer hyperlink (at the bottom of the post), i.e. see on Alesksei's reply to your Get minimum value by column by file thread

    Re. How to add a new column formula that modifies content

    This is confusing. The title of your thread says "How to add a new column" and in your problem description you say "How can I write a formula in power query that modifies..."

    1. What do you exactly want to do? Add a column or modify an existing one???
    2. You say "I have a column with numbers as text.." and later you say "How can I write a formula in power query that modifies all the numbers to text...". Clarify please

    Friday, January 24, 2020 2:53 AM
  • Oh yes! I figured it out. I just needed to use a conditional column that replaced the value and then change data type to whole number. Boom!
    • Marked as answer by Albo44 Friday, January 24, 2020 5:31 AM
    Friday, January 24, 2020 5:31 AM
  • You don't need an additional conditional in that case. Assuming column is [Data]:

        Replace = Table.ReplaceValue(PreviousStepName, ">300", 0, Replacer.ReplaceValue, {"Data"}),
        ChangedType = Table.TransformColumnTypes(Replace,{{"Data", type number}})
    

    • Proposed as answer by Lz._ Monday, February 3, 2020 10:13 PM
    Friday, January 24, 2020 6:57 AM