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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
zenz
Frequent Visitor

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

zenz_1-1718928191074.png

 

 

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! 

 

3 ACCEPTED SOLUTIONS
zenz
Frequent Visitor

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

View solution in original post

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.
 

View solution in original post

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

View solution in original post

5 REPLIES 5
zenz
Frequent Visitor

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

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

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

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

Ibanez2000_2-1718940656812.png

 

Ibanez2000_3-1718940713271.png

Result:

Ibanez2000_4-1718940766761.png

2. Change column type to date

Ibanez2000_5-1718940798705.png

 

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

Ibanez2000_7-1718941187867.png

Result:

Ibanez2000_8-1718941252412.png

 

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

 

 

 

 

v-linyulu-msft
Community Support
Community Support

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:

vlinyulumsft_0-1718940011586.png

vlinyulumsft_1-1718940018626.png

vlinyulumsft_2-1718940031294.png

1.First we need to import this external file:

vlinyulumsft_3-1718940062868.png

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:

vlinyulumsft_4-1718940097975.png

Then expand the required data:

vlinyulumsft_5-1718940113173.png

Here are the final results:

vlinyulumsft_6-1718940133402.png

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:

vlinyulumsft_7-1718940152257.png

Then proceed to the merge query:

vlinyulumsft_10-1718940209443.png

Here are the final results:

vlinyulumsft_9-1718940196098.png

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.

 

 

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors