Create Date custom column from source name.

[Anonymous]

Hi,

Help me to create Mcode using adding custom column from 'Source name'

that year take first 4 number of source name which is 2024(YYYY),

if source name contains Q1 then 03/31(MM/DD),

if source name contains Q2 then 06/30(MM/DD),

if source name contains Q3 then 09/30(MM/DD),

if source name contains Q4 then 12/31(MM/DD),

e.g. for below example > 12/31/2024 in 'Date' format in custom column which will be created by Mcode.

[saud968 · Accepted solution]

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!

[Ashish_Mathur · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[saud968 · Accepted solution]

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!

[Anonymous]

Can you help me write a full code? 

maybe you can write code referring to below my current code pls. 

FYI,
#"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns",{"Source name", "Sheet name", "Internal Site ID", "Category", "공사번호#(lf)Service No."}),
#"Added Conditional Column" = (Solution)
#"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"

[Anonymous · Accepted solution]

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

[Anonymous]

Thanks it works and has accepted.