CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktopdata-cleaningm
Ankur
Ankur

Reputation: 11739

How to replace a column value using PowerQuery editor

I'm relatively new to Power Query. I'm looking for the best way to replace a column's value as below. The column has date values in a mixed format such as below.

09/16/2022
09/20/2022
09/26/2022
09/30/2022
10-01-2022
10-03-2022
10-05-2022

I'm looking to standardize and make the format generic as below.

09-16-2022
09-20-2022
09-26-2022
09-30-2022
10-01-2022
10-03-2022
10-05-2022

It seems one of the ways to implement this is to use Advanced Editor and build M queries to implement the replacement, functions like Table.TransformColumns and Text.Replace.

'can't figure out the exact code to be used with this or if there is a better way.

Looking for suggestions. Thanks.

Upvotes: 1

Views: 517

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

In M code you could use:

Table.TransformColumns(#"Previous Step",{{"Column Name", each Text.Replace(_,"/","-")}})

As an example:

let

//create sample table
    Source = Table.FromColumns(
               {{"09/16/2022",
                "09/20/2022",
                "09/26/2022",
                "09/30/2022",
                "10-01-2022",
                "10-03-2022",
                "10-05-2022"}},
                type table[dates=text]),

//replace "/" with "-"
    Normalize = Table.TransformColumns(Source,{{"dates", each Text.Replace(_,"/","-"), type text}})
in
    Normalize

Source
enter image description here

Results
enter image description here

Notes:

  • Original data is dates as text strings
  • Final data is also dates as text strings
  • To convert the strings to dates, you could use, instead, something like: Table.TransformColumns(Source,{{"dates", each Date.From(_, "en-US"), type date}}) but the separator would be in accord with your Windows Regional date settings.

Upvotes: 1

davidebacci
davidebacci

Reputation: 30174

If you're a beginner, let the UI write this code for you. Highlight the column by clicking the column header, go to Transform on the ribbon and click Replace Values. Replace "-" with "/" and click OK.

Finally right click the column header again, click Change Type and then select Date.

Upvotes: 4

Related Questions