Execute functions one after the other

[crowsname]

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)

[Joe_Barry · Accepted solution]

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)

[dufoq3 · Accepted solution]

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

[crowsname]

Wow, thank you! It works and it is so much shorter! 🙂

[dufoq3]

You're welcome. There are always many ways how to achieve same result 😉

[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)

[crowsname]

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.

[Joe_Barry]

Hi  @crowsname 

In Power Query create a new cutom column and enter this code. 

if Text.Length(Text.From([DateInteger])) = 8 then Text.From([DateInteger]) else "0"  & Text.From([DateInteger])

Format it as text

Thanks

Joe

[crowsname]

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 · Accepted solution]

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)

[crowsname]

Thank you!

It works!

[crowsname]

Thank you!

I will test it!