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.
Hello,
I am attempting to use Power Query to do the following in plain langue: If a date appears at the beginning of the month, replace it with the last date of the previous month (essentially, push it back one day). Leave all other dates alone.
So far, I have deduced the following:
= if Date.Day([End Current]) = 1 then Table.ReplaceValue(#"Changed Type" ,each [End Current] ,each Date.AddDays([End Current], -1) ,Replacer.ReplaceValue,{"End Current"}) else [End Current]
But I am receiving the error "Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"
And I can't trace it.
Anybody have an idea about how to resolve?
FYI: Date.Day function returns a numeric for the date referenced, and Date.AddDays adds or removes an integer number of days according to the argument given (in this case, -1).
What am I missing?
THANK YOU!
Solved! Go to Solution.
Can you please try this... Replacer.ReplaceValues may have some issues in conditional replace
#"OutP" =
Table.RenameColumns(
Table.RemoveColumns(
Table.AddColumn( #"Changed Type", "NewDate",
each if Date.Day([End Current]) = 1 then
Date.AddDays([End Current], -1) else [End Current]),
{"End Current"}),{{"NewDate", "End Current"}})
Can you please try this... Replacer.ReplaceValues may have some issues in conditional replace
#"OutP" =
Table.RenameColumns(
Table.RemoveColumns(
Table.AddColumn( #"Changed Type", "NewDate",
each if Date.Day([End Current]) = 1 then
Date.AddDays([End Current], -1) else [End Current]),
{"End Current"}),{{"NewDate", "End Current"}})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |