Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
41 | |
21 | |
20 | |
20 | |
13 |
User | Count |
---|---|
67 | |
53 | |
42 | |
28 | |
22 |