Forum Discussion

zenz's avatar
zenz
Frequent Visitor
2 years ago
Solved

Create a custom column but no function is available

Hi there,    when i create a custome column in the power query editor, the functions available are very limited (see below).      what i want is to extract parts of the the [Source.Name], ...
Need Help
Tips and Tricks
  • zenz's avatar
    zenz
    2 years ago

    Thank you, both Anonymous and WanderingBI . Appreciated. It turns out that i can just use the below syntax to achieve the same. 

     

    Date.FromText(
    Text.Middle([Source.Name],5,8)
    )

  • WanderingBI's avatar
    WanderingBI
    2 years ago

    Thats a nice solution!

     

    One handy tip: You can also in the same line add the specification for the data type (data):

    Table.AddColumn(Source, "Custom", each Date.FromText(
    Text.Middle([Source.Name],5,8)
    ), type date)
     
    This way you can save yourself one additional step in the query.
     
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI,zenz 

    We are very glad to know that the issue has been resolved. If you wish, consider accepting your solution as a solution that will also benefit other community members who have the same problem as you and find a solution faster.

     

    Of course, if there is anything else we can do for you, please do not hesitate to contact us.

     

    Looking forward to your reply.

     

    Best Regards,

    Leroy Lu

WanderingBI's avatar
WanderingBI
Resolver III
2 years ago

Hi! 

 

Lets first take a look on how to accomplish the same thing by using the GUI:

1. Create a new column by extracting text after delimiter "_"

 

Result:

2. Change column type to date

 

You can analyse the code in the advanced editor to transfer the contents to a custom column if you want:

Result:

 

Text.AfterDelimiter([Source.Name], "_", 0)