cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Code for Power Query

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!

1 ACCEPTED 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"})

View solution in original post

2 REPLIES 2
Super User III
Super User III

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"})

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors