Create Date custom column from source name.

I can help you create the M code for this. Here's how you can add a custom column in Power Query to generate the date based on the 'Source name':

Open Power Query Editor.
Go to the Add Column tab.
Click on Custom Column.
Enter the following M code in the Custom Column formula box:
let
Source = ... // Your data source here
AddCustom = Table.AddColumn(Source, "Custom Date", each
let
SourceName = [Source name],
Year = Text.Start(SourceName, 4),
Quarter =
if Text.Contains(SourceName, "Q1") then "03/31"
else if Text.Contains(SourceName, "Q2") then "06/30"
else if Text.Contains(SourceName, "Q3") then "09/30"
else if Text.Contains(SourceName, "Q4") then "12/31"
else null,
DateString = Quarter & "/" & Year,
DateValue = Date.FromText(DateString)
in
DateValue
)
in
AddCustom
This code will create a new column named "Custom Date" with the date formatted as specified based on the 'Source name'. Make sure to replace ... // Your data source here with your actual data source.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom2" = Table.AddColumn(Source, "Date", each Date.EndOfMonth(#date(Number.From(Text.Start([Source name],4)),if Text.Contains([Source name],"Q1") then 3 else if Text.Contains([Source name],"Q2") then 6 else if Text.Contains([Source name],"Q3") then 9 else 12,1)))
in
    #"Added Custom2"

Hope this helps.

Hi Anonymous ,

Take the code given by Ashish as an example:

let
......,
#"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns",{"Source name", "Sheet name", "Internal Site ID", "Category", "공사번호#(lf)Service No."}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Other Columns2", "Date", each Date.EndOfMonth(#date(Number.From(Text.Start([Source name],4)),if Text.Contains([Source name],"Q1") then 3 else if Text.Contains([Source name],"Q2") then 6 else if Text.Contains([Source name],"Q3") then 9 else 12,1)))
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"공사번호#(lf)Service No.", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
#"Added Index"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum