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

Query Editor-Replace Errors in Date column with latest date

Hello,

 

i have a file where in one of the columns i have dates or text value "History".

 

What i want to do in the query editor is to replace the "History" with latest date in the column.

 

What i tried was :

1. Convert the column to "Date" datatype which result with the "History" cells changing to Error

2. Replacing Errors with latest value from the column.

 

I looked into how filtering latest date transform to code in advanced editor and came up with below:

 

 

 #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", let latest = List.Max(#"Changed Type"[Date when data was loaded to database]) in each {{"Date",latest}})

 

/

 

#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", let latest = List.Max(#"Changed Type"[Date when data was loaded to database]) in each Date.From(latest))

 

But they are giving error :

error.png

 

Can you help me with correcting it ? 

 

Below example dataset :

dataset.jpg

1 ACCEPTED SOLUTION

Hi @Wojcik_J ,

I would write it like so:

 

Table.ReplaceErrorValues(#"Changed Type", {{"Date", List.Max(Table.RemoveRowsWithErrors(#"Changed Type", {"Date"})[Date])}})

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@ImkeF 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Wojcik_J ,

I would write it like so:

 

Table.ReplaceErrorValues(#"Changed Type", {{"Date", List.Max(Table.RemoveRowsWithErrors(#"Changed Type", {"Date"})[Date])}})

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Works, thank you for your support ! 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.