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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Peter_Yaacoub
Frequent Visitor

Date Column Returning null

Hi everyone!

I am  trying to create a date column based on my YearDate column (snapshot below). If YearDate Column is Current Year -1, I want to replace it  with [Merged], otherwise replace with [Merged.1].

 

Unfortunately the formula I am using returns null. Your help is much appreciated.

 

Thanks,

Peter

 

Peter_Yaacoub_0-1655458312255.png

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Insert this statement where #"Changed Type" is your previous step

= Table.ReplaceValue(#"Changed Type",each [YearDate],each if Date.Year([YearDate])=Date.Year(DateTime.FixedLocalNow())-1 then [Merged]
else [Merged.1],Replacer.ReplaceValue,{"YearDate"})

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NtQ3MjAyVNJRcgRiJyAGc2N1UCWdgdgFm6QRUNAViN0gkkYYku5A7IFL0hOIvXBJegOxD1wyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearDate = _t, Merged = _t, Merged.1 = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YearDate", type date}, {"Merged", type text}, {"Merged.1", type text}, {"Year", Int64.Type}}),
    Custom1 = Table.ReplaceValue(#"Changed Type",each [YearDate],each if Date.Year([YearDate])=Date.Year(DateTime.FixedLocalNow())-1 then [Merged]
else [Merged.1],Replacer.ReplaceValue,{"YearDate"})
in
    Custom1

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

Insert this statement where #"Changed Type" is your previous step

= Table.ReplaceValue(#"Changed Type",each [YearDate],each if Date.Year([YearDate])=Date.Year(DateTime.FixedLocalNow())-1 then [Merged]
else [Merged.1],Replacer.ReplaceValue,{"YearDate"})

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NtQ3MjAyVNJRcgRiJyAGc2N1UCWdgdgFm6QRUNAViN0gkkYYku5A7IFL0hOIvXBJegOxD1wyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearDate = _t, Merged = _t, Merged.1 = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YearDate", type date}, {"Merged", type text}, {"Merged.1", type text}, {"Year", Int64.Type}}),
    Custom1 = Table.ReplaceValue(#"Changed Type",each [YearDate],each if Date.Year([YearDate])=Date.Year(DateTime.FixedLocalNow())-1 then [Merged]
else [Merged.1],Replacer.ReplaceValue,{"YearDate"})
in
    Custom1

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors