Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
eyespike1
Frequent Visitor

Replace Value Based on latest date

Hi, I'm looking to replace values based off of the date column.

For each Name, I want the location to be updated based off of the latest Date.

This is the starting data:

eyespike1_0-1624550686735.png

This is the result:

eyespike1_1-1624550842244.png

Working in Excel Power Query

1 ACCEPTED SOLUTION

=Table.Combine(Table.Group(Source,"Name",{"n",each let NewLocation=Table.First(Table.Sort(_,{"Date",1}))[Location] in Table.ReplaceValue(_,null,NewLocation,(x,y,z)=>z,{"Location"})})[n])

View solution in original post

8 REPLIES 8
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1624581848472.png

 

Daniel,

Thanks for your reply. You code worked, unfortunately I didn't clearly state that I would have multiple "Names".

Is it possible for you to modify so we are getting the most recent location by date for each name?

Somebody's locations would be Location2

Somebody2's locations would be Location6

Somebody3's locations would be Location13

 

eyespike1_2-1624648101040.png

 

 

Thank you!

 

 

=Table.Combine(Table.Group(Source,"Name",{"n",each let NewLocation=Table.First(Table.Sort(_,{"Date",1}))[Location] in Table.ReplaceValue(_,null,NewLocation,(x,y,z)=>z,{"Location"})})[n])

Hello, thank you for the very helpful formula! I have the exact same issue as eyespike1, and this forum has been a blessing.

 

One stumbling block I have encountered: Are you able to advise on excluding nulls as the most recent value? Example: 

K_L_G_0-1687292886777.png

 

In this scenario, I want:

Somebody's locations would be Location3

Somebody2's locations would be Location6

Somebody3's locations would be Location12

 

Edit: The only option I have found is adding a helper column that tags rows w/ number in "Location" as "1" and nulls as "2". Then modifying the Table.Sort step to sort by both the helper column and Date column. Is the the only / best way?

 

thank you!

That worked! Thank you so much!

Pragati11
Super User
Super User

Hi @eyespike1 ,

 

I have considered following input data: (just added an Index column for clarity)

Pragati11_0-1624562106959.png

Then I created a calculated column as follows using DAX:

newLocationcol =
CALCULATE(MAX('Table'[Location]), FILTER('Table', 'Table'[Date] = MAX('Table'[Date])))
 
Now added this column to the table visual and you get the respective output:
Pragati11_1-1624562207019.png

So, basically a new column calculation is created.

 

Thanks,

Pragati

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi Pragati,

Thank you for your reply. I tried your solution but since this is in Excel and not PowerBi I don't think it will work.

I also didn't clearly state that I would have multiple "Names".

eyespike1_0-1624650752314.png

Somebody's locations would be Location2

Somebody2's locations would be Location6

Somebody3's locations would be Location13

Thanks!

Seth

=Table.Combine(Table.Group(Source,"Name",{"n",each let NewLocation=Table.First(Table.Sort(_,{"Date",1}))[Location] in Table.ReplaceValue(_,null,NewLocation,(x,y,z)=>z,{"Location"})})[n])

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors