Forum Discussion

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

Anonymous

Not applicable

2 years ago

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: