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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

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.

 

CreationFinishedNew column
10.02.2020    no
06.02.2019 no
18.01.2020 23.04.2020 Apr-20
27.08.201809.01.2020 Jan-20
28.08.2019 no
27.08.2019 no
28.08.201704.04.2019Apr-19
27.08.201723.03.2019Mars 2019
27.08.201812.05.2020May-20
27.08.2019  
25.06.2019  
12.09.201914.12.2019Dec-19
14.04.201915.10.2019Oct-19

 

Thanks!

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Microsoft Employee
Microsoft Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


harshnathani
Community Champion
Community Champion

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)

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)

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors