Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
crowsname
Regular Visitor

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)

2 ACCEPTED SOLUTIONS

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)

View solution in original post

dufoq3
Super User
Super User

Hi @crowsname

 

try this: (change [Column1] to your column if necessary)

 

Date.FromText(Text.PadStart(Text.From([Column1]), 8, "0"), [Format="ddMMyyyy"])

 

 

Result:

dufoq3_0-1710935309673.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

10 REPLIES 10
dufoq3
Super User
Super User

Hi @crowsname

 

try this: (change [Column1] to your column if necessary)

 

Date.FromText(Text.PadStart(Text.From([Column1]), 8, "0"), [Format="ddMMyyyy"])

 

 

Result:

dufoq3_0-1710935309673.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Joe_Barry
Solution Supplier
Solution Supplier

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.

Joe_Barry
Solution Supplier
Solution Supplier

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

 

 

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.

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)

Thank you!

It works!

Thank you!

 

I will test it!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors