Forum Discussion
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], 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!
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)
)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.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
5 Replies
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)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.
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)
)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.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