How to replace a column value using PowerQuery editor

Question

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.

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

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.

davidebacci · Accepted Answer · 2022-10-15 07:38:49Z

4

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.

answered Oct 15, 2022 at 7:38

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2022-10-16 01:45:03Z

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

Results

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.

Collectives™ on Stack Overflow

How to replace a column value using PowerQuery editor

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related