Create new conditional column that gives Month and Year of date column if one column is in that mont

[Anonymous]

Hi,

I'm would like to create a new column (prefferably in power query) that gives me the month name and year if column "Finished" is greater than "Creation", if not I would like that column to return blank or some text.

Below is an example of what I have and would like to achieve.

Creation	Finished	New column
10.02.2020		no
06.02.2019		no
18.01.2020	23.04.2020	Apr-20
27.08.2018	09.01.2020	Jan-20
28.08.2019		no
27.08.2019		no
28.08.2017	04.04.2019	Apr-19
27.08.2017	23.03.2019	Mars 2019
27.08.2018	12.05.2020	May-20
27.08.2019
25.06.2019
12.09.2019	14.12.2019	Dec-19
14.04.2019	15.10.2019	Oct-19

Thanks!

[mahoneypat · Accepted solution]

You can use this M code to do this in query in a Custom Column.  Below is M code for a query to see an example.

For Custom Column

=Text.Combine({Date.ToText([Finished], "MMM"), "-", Date.ToText([Finished], "yy")})

Example Query

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0gEyjSHMWB2QuBFMHMo3NIUJGMH0gCSM9E0QCmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Creation = _t, Finished = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Creation", type date}, {"Finished", type date}}),
#"Added Custom Column" = Table.AddColumn(#"Changed Type", "Finish Month Year", each Text.Combine({Date.ToText([Finished], "MMM"), "-", Date.ToText([Finished], "yy")}), type text)
in
#"Added Custom Column"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[harshnathani]

Hi @Anonymous ,

Create a Calculated Column

YYMM = FORMAT(Table[Creation],"YYYY-MM")

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani]

Hi @Anonymous ,

In the above case since you want in the format MMM-YYYY.

YYMM = FORMAT('Table'[finished],"MMM-YYYY")

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)