Forum Discussion
Execute functions one after the other
Hello everyone,
do I see it correctly that if I want to execute 2 functions one after the other and the second one should continue working with the result of the first one, that I have to create a user-defined function?
Example:
I have dates in a column in the following form:
8012024
13082024
1052024
14052024
To be able to work with Date.FromText, I am missing the leading 0 in the first and third value. That's why I first wanted to add the 0 with Text.Insert and then execute the Date function with the new value.
Maybe I'm thinking too complicated in general and someone has a simpler way.
Have a nice day.
Michael Hoffmann
Translated with DeepL.com (free version)
I wraped them in one step
Text.Start(if Text.Length(Text.From([DateInteger])) = 8 then Text.From([DateInteger]) else "0" & Text.From([DateInteger]), 2) & "/" & Text.Range(if Text.Length(Text.From([DateInteger])) = 8 then Text.From([DateInteger]) else "0" & Text.From([DateInteger]), 2, 2) & "/" & Text.End(if Text.Length(Text.From([DateInteger])) = 8 then Text.From([DateInteger]) else "0" & Text.From([DateInteger]),4)Hi crowsname,
try this: (change [Column1] to your column if necessary)
Date.FromText(Text.PadStart(Text.From([Column1]), 8, "0"), [Format="ddMMyyyy"])Result:
Whole code: (this add new column)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAwNDIwMlGK1YlWMjQ2sEBwDEwRbBMYJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.FromText(Text.PadStart(Text.From([Column1]), 8, "0"), [Format="ddMMyyyy"])) in #"Added Custom"If you want to transform [Column1]:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAwNDIwMlGK1YlWMjQ2sEBwDEwRbBMYJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), TransformedToDate = Table.TransformColumns(Source, {{"Column1", each Date.FromText(Text.PadStart(Text.From(_), 8, "0"), [Format="ddMMyyyy"]), type date}}) in TransformedToDate
10 Replies
- dufoq3
Community Champion
Hi crowsname,
try this: (change [Column1] to your column if necessary)
Date.FromText(Text.PadStart(Text.From([Column1]), 8, "0"), [Format="ddMMyyyy"])Result:
Whole code: (this add new column)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAwNDIwMlGK1YlWMjQ2sEBwDEwRbBMYJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.FromText(Text.PadStart(Text.From([Column1]), 8, "0"), [Format="ddMMyyyy"])) in #"Added Custom"If you want to transform [Column1]:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAwNDIwMlGK1YlWMjQ2sEBwDEwRbBMYJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), TransformedToDate = Table.TransformColumns(Source, {{"Column1", each Date.FromText(Text.PadStart(Text.From(_), 8, "0"), [Format="ddMMyyyy"]), type date}}) in TransformedToDate Hello,
Thank you very much!
Unfortunately, that's not what I need.
I also want to convert the text value into a date in one step. Otherwise I need another column that converts the text value into a date.- Joe_Barry
Solution Sage
I wraped them in one step
Text.Start(if Text.Length(Text.From([DateInteger])) = 8 then Text.From([DateInteger]) else "0" & Text.From([DateInteger]), 2) & "/" & Text.Range(if Text.Length(Text.From([DateInteger])) = 8 then Text.From([DateInteger]) else "0" & Text.From([DateInteger]), 2, 2) & "/" & Text.End(if Text.Length(Text.From([DateInteger])) = 8 then Text.From([DateInteger]) else "0" & Text.From([DateInteger]),4)
- Joe_Barry
Sorry about that.
After the steps above you will need to add another column and convert to date
Text.Start([Custom], 2) & "/" & Text.Range([Custom], 2, 2) & "/" & Text.End([Custom],4)Thank you.
Unfortunately, that's not the solution I'm looking for. I am familiar with this method.
But I am looking for a solution without creating 2 columns, but doing it in one step. That's why I asked whether this is only possible with a user-defined function.
