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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tonyk86
Helper I
Helper I

Repeating dates based on row criteria

Hi all,

    Hoping someone can help me. So basically I have this survey dataset (see sample below). The Walk Date column doesn't actually exists in the original dataset, however it exists as one of the values in the Step Answer column. The Step Answer column contains multiple types of values (mostly text and dates). 

 

     What I want to do is extract the date from the Step Answer column which is pretty easy, however, here is the tricky part now. I want to repeat that Walk Date for each of the unique Task ID.

 

 

Example: if Store 10 has a task ID of a39b-036c1df3a4f1. The Walk Date was 10/14/2020. I want to repeat that walk date for each row that contains that same task ID. See the column in red.  Hope someone can help out as I have been stuck on this for 45 minutes. Thanks! 

 

 

Task IDWalk Date (What I extracted)Walk Date ( What I want)LocationQuestion Step Answer
a39b-036c1df3a4f110/14/202010/14/2020101 - When was this location walked?10/14/2020
a39b-036c1df3a4f1Null10/14/2020102 - Was all the schedules posted on time?No
aaa8-98f60e23b0b110/20/2020 10/20/2020151 - When was this location walked?10/20/2020
aaa8-98f60e23b0b1Null 10/20/2020152 - Was all the schedules posted on time?Yes
1 ACCEPTED SOLUTION
edhans
Super User
Super User

Can you just right-click on the Walk Date columnj @tonyk86 , select Fill from the right-click menu, then Down from the submenu?

It will fill the date down filling all nulls until it hits another date, then it stops and repeats the process.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

Great @tonyk86 - glad to be of assistance!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
tonyk86
Helper I
Helper I

Hi @edhans,

  Unfortunately, some locations have more than the 2 questions. Thanks for the suggestion. So when I do fill up/fill down (or both), it would sometimes put the incorrect date for at the start of the new task ID. This is my dilemma.  See the screenshot below. Any other suggestions?

 

tonyk86_0-1604683005800.png

 

Hi @edhans,

     I was able to find a solution. What I did was filtered the question Walk Date which would remove the date discrepancy between the unique Task ID when I do the fill down. Thanks for your help!

edhans
Super User
Super User

Can you just right-click on the Walk Date columnj @tonyk86 , select Fill from the right-click menu, then Down from the submenu?

It will fill the date down filling all nulls until it hits another date, then it stops and repeats the process.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors