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 All
I started working a task by creating calculated columns and it's causing problems with other steps due to circular references, etc. I would like to start over and just fix everything by doing it in Power Query. Here is the scenario:
I have a Column called "Period" which has dates. I need to replace certain dates in this column with the word "Baseline." Condition has to be met in another column for this to happen. It goes like this:
If the "ApplicationID" column has 34, 35, or 36 and the "Period" column has the date "7/11/2019" then change the "7/11/2019" to say "Baseline." That's it----that's all I need.
Is there an M Code that can do this in Power Query. Remember that I have to make this change in the Period Column. I dont want to create another column becuase this report is big and many queries already exist. I am just doing this as a band-aid at the very last step.
Extremely grateful for any help!
Solved! Go to Solution.
Thanks --- I was finally able to achieve it this way:
= Table.ReplaceValue(#"Changed Type",each [Period], each if [Period]="7/11/2019" and [ApplicationId]>=34 and [ApplicationId]<=36 then "Baseline" else [Period],Replacer.ReplaceText,{"Period"})
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZR0lEy1zc01DcyMLRUitUBCplChIyQhMwwVZljqrJAFYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ApplicationID = _t, Period = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ApplicationID", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","7/11/2019",each if [ApplicationID] = 34 or [ApplicationID] =35 or [ApplicationID] =36 then "Baseline" else [Period],Replacer.ReplaceText,{"Period"})
in
#"Replaced Value"
Thanks --- I was finally able to achieve it this way:
= Table.ReplaceValue(#"Changed Type",each [Period], each if [Period]="7/11/2019" and [ApplicationId]>=34 and [ApplicationId]<=36 then "Baseline" else [Period],Replacer.ReplaceText,{"Period"})
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 |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |