Power Query - Transform multiple columns at once to same format-CodePudding

I have a table in Power Query that has multiple date columns in a specific Julian Date format. I would like to change all of them at once into the classic date format by using this function:

Date.AddDays(#date(Number.RoundDown([Date1]/1000) 1900,1,1),Number.Mod([Date1],1000)-1)

For now I doing this steps for every column:

Add custom column with the function above
Delete old column
Rename new column

Is there any way to do this more harmonized for all selected columns at once?

Example Data:

CodePudding user response：

You could create a function in Power Query.

New Source -> Blank Query

Than e.g. for testing you could use this example. Write in advanced editor in this new blank query:

let YourFormula = (input) =>
        input * 2
in YourFormula

Now it looks like:

In this field you can test your function.

After that you can go into your main table and add in the advanced editor at the end of code:

Transform = Table.TransformColumns(#"Removed Columns2", {{"PROJECTID", Query1}, {"ACCRUED", Query1}})
in
    Transform

This transforms the selected columns inplace. No need for adding a new one, deleting the old one and renaming.

Update according comments, now with your specific data.

Formula of the function:

let YourFormula = (input) =>
    Date.AddDays(#date(Number.RoundDown(input/1000) 1900,1,1),Number.Mod(input,1000)-1)
in
    YourFormula

Code in the main table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNDAwVNIBMQxNjZRidaBiRjAxM4SYMUzMAiFmAhEzMjVQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", Int64.Type}, {"Date2", Int64.Type}}),
    Transform = Table.TransformColumns(#"Changed Type", {{"Date1", Query1}, {"Date2", Query1}}),
    #"Changed Type1" = Table.TransformColumnTypes(Transform,{{"Date2", type date}, {"Date1", type date}})
in
    #"Changed Type1"

Table before and after: