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

Replace Values in Query Editor for Specific Cells

Is there a way to replace values in a column for specific cells in query editor?  For example, I have one column called "Meetings".  There are two types of meetings "Planned Meetings" and "Completed Meetings".  I  also have a "Planned Meeting Date" column.

 

There are instances of "Planned Meetings" that are earlier than Today's date, that should be changed to "Completed Meetings".  However, I do not want to changed the Planned Meeting that is after today's date. How can I do this via the query editor.  Thanks!

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Replace Values in Query Editor for Specific Cells

Working with date and datetime fields in M can be a little frustrating. While not a complete solution, here is an example of filtering a query based upon the local date and time (today). You should be able to use this to achieve what you want to do:

 

= Table.SelectRows(#"Removed Columns", each [Date] < #date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow())))

 

Basically, this selects out the rows that are earlier than today and then you could do a find and replace on those items or base a new column on similar logic to this.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
Highlighted
Super User IV
Super User IV

Re: Replace Values in Query Editor for Specific Cells

Working with date and datetime fields in M can be a little frustrating. While not a complete solution, here is an example of filtering a query based upon the local date and time (today). You should be able to use this to achieve what you want to do:

 

= Table.SelectRows(#"Removed Columns", each [Date] < #date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow())))

 

Basically, this selects out the rows that are earlier than today and then you could do a find and replace on those items or base a new column on similar logic to this.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors