Create a custom column but no function is available

[zenz]

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], and rearrange to get to the date from the file name. 

for example, the file name is "data_20240620" in the column [Source.Name], then i want to extract the date from it, and then create a date column as a custom column. 

Please help. Thank you! 

[zenz · Accepted solution]

Thank you, both @v-linyulu-msft 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 · Accepted solution]

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.

[zenz · Accepted solution]

Thank you, both @v-linyulu-msft 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)
)

[v-linyulu-msft · Accepted solution]

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

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.

[WanderingBI]

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)

[v-linyulu-msft]

Hi,@zenz 

Regarding the issue you raised, my solution is as follows:

First, I created an excel file containing the date as an external file according to your requirements, and then I created the following table in the desktop:

1.First we need to import this external file:

2.Secondly, if your need is to create one-to-many data, here is the corresponding solution:

Create custom columns and merge directly with merge queries:

Then expand the required data:

Here are the final results:

3.If your requirement is one-to-one, you will need to create a column with the same id for the two corresponding values, here I will use the serial number instead:

Then proceed to the merge query:

Here are the final results:

4.Here is the relevant documentation:

Merge queries overview - Power Query | Microsoft Learn

Append queries - Power Query | Microsoft Learn

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.