March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 ID | Walk Date (What I extracted) | Walk Date ( What I want) | Location | Question | Step Answer |
a39b-036c1df3a4f1 | 10/14/2020 | 10/14/2020 | 10 | 1 - When was this location walked? | 10/14/2020 |
a39b-036c1df3a4f1 | Null | 10/14/2020 | 10 | 2 - Was all the schedules posted on time? | No |
aaa8-98f60e23b0b1 | 10/20/2020 | 10/20/2020 | 15 | 1 - When was this location walked? | 10/20/2020 |
aaa8-98f60e23b0b1 | Null | 10/20/2020 | 15 | 2 - Was all the schedules posted on time? | Yes |
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGreat @tonyk86 - glad to be of assistance!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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?
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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |