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:
Is there any way to do this more harmonized for all selected columns at once?
Example Data:
You could create a function in Power 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:
