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
Draco
Frequent Visitor

repeating status from one record to the next

All, I’m still learning DAX and got this task; I’m trying to repeat the status value from 1 record to the next. YEC Comments is a separate SharePoint list and uses Power App and collects Project, DTE/Tm, Status and Comments.  Comments are imported into Power BI as its own table.

The data are 2 tables, comments and main (primary). Both table are join by DteTM column. When the various fields are pulled into the table view you get the first image (blanks in the status column). The second picture is the desired outcome. The other 2 are csv extract.

  1. When there’s no status, by default Status = ‘Not Closed’
  2. The status will repeat itself to the next record until the next status change.
  3. There are multiple projects with the same date/time
  4. Project and DTETm are the join fields between the two list. Note: I do not have pro license.

I would have uploaded the file, but don't see a way.

Current output

Draco_0-1698420788974.png

 

Desired output

Draco_2-1698421397068.png

Comments.csv

Project,DteTm,Comments,Status,Title
Proj 1,9/28/2023 9:30,once upon a time,Closed,
Proj 2,9/28/2023 9:30,sleepy,Not Closed,
Proj 2,10/1/2023 0:45,test,Closed,
Proj 3,9/28/2023 9:30,test,Closed,
Proj 3,9/30/2023 22:00,test,Closed,
Proj 3,9/30/2023 17:00,test,Minor Issues,
Proj 3,9/29/2023 9:30,test,Not Closed,
Proj 3,9/30/2023 23:00,test,Not Closed,
Proj 3,10/1/2023 0:45,tst,Closed,

main.csv

Project,DTETm,Sales,Exp
Proj 1,9/27/2023 9:00,$273 ,$538.09 
Proj 1,9/28/2023 9:30,$273 ,$540.01 
Proj 1,9/28/2023 13:00,$273 ,$540.72 
Proj 1,9/28/2023 16:00,$273 ,$540.99 
Proj 1,9/29/2023 8:00,$273 ,$541.41 
Proj 1,9/29/2023 9:30,$273 ,$541.45 
Proj 1,9/29/2023 13:00,$273 ,$541.47 
Proj 1,9/29/2023 16:00,$273 ,$541.55 
Proj 1,9/30/2023 9:30,$273 ,$541.55 
Proj 1,9/30/2023 13:00,$273 ,$541.55 
Proj 1,9/30/2023 15:00,$273 ,$541.61 
Proj 1,9/30/2023 16:00,$273 ,$541.61 
Proj 1,9/30/2023 17:00,$273 ,$541.61 
Proj 1,9/30/2023 18:00,$273 ,$541.61 
Proj 1,9/30/2023 19:00,$273 ,$541.61 
Proj 1,9/30/2023 20:00,$273 ,$541.61 
Proj 1,9/30/2023 21:00,$273 ,$541.61 
Proj 1,9/30/2023 22:00,$273 ,$541.61 
Proj 1,9/30/2023 23:00,$273 ,$541.61 
Proj 1,10/1/2023 0:15,$273 ,$541.61 
Proj 1,10/1/2023 0:45,$273 ,$541.61 
Proj 1,10/2/2023 16:00,$273 ,$541.61 
Proj 1,10/3/2023 10:30,$273 ,$541.61 
Proj 1,10/10/2023 11:30,$273 ,$541.61 
Proj 2,9/27/2023 9:00,$204 ,$223.22 
Proj 2,9/28/2023 9:30,$204 ,$224.37 
Proj 2,9/28/2023 13:00,$204 ,$226.40 
Proj 2,9/28/2023 16:00,$204 ,$226.40 
Proj 2,9/29/2023 8:00,$204 ,$226.62 
Proj 2,9/29/2023 9:30,$204 ,$226.63 
Proj 2,9/29/2023 13:00,$204 ,$226.65 
Proj 2,9/29/2023 16:00,$204 ,$226.99 
Proj 2,9/30/2023 9:30,$204 ,$227.77 
Proj 2,9/30/2023 13:00,$204 ,$227.78 
Proj 2,9/30/2023 15:00,$204 ,$227.79 
Proj 2,9/30/2023 16:00,$204 ,$227.79 
Proj 2,9/30/2023 17:00,$204 ,$228.02 
Proj 2,9/30/2023 18:00,$204 ,$228.02 
Proj 2,9/30/2023 19:00,$204 ,$228.05 
Proj 2,9/30/2023 20:00,$204 ,$228.08 
Proj 2,9/30/2023 21:00,$204 ,$228.08 
Proj 2,9/30/2023 22:00,$204 ,$228.08 
Proj 2,9/30/2023 23:00,$204 ,$228.08 
Proj 2,10/1/2023 0:15,$204 ,$228.08 
Proj 2,10/1/2023 0:45,$204 ,$228.08 
Proj 2,10/2/2023 16:00,$204 ,$228.08 
Proj 2,10/3/2023 10:30,$204 ,$228.08 
Proj 2,10/10/2023 11:30,$204 ,$228.08 
Proj 3,9/30/2023 22:00,"$7,249 ","$13,229.37 "
Proj 3,9/30/2023 23:00,"$7,249 ","$13,229.45 "
Proj 3,10/1/2023 0:15,"$7,249 ","$13,229.45 "
Proj 3,10/1/2023 0:45,"$7,249 ","$13,229.45 "
Proj 3,10/2/2023 16:00,"$7,249 ","$13,231.51 "
Proj 3,10/3/2023 10:30,"$7,249 ","$13,231.51 "
Proj 3,10/10/2023 11:30,"$7,249 ","$13,231.51 "

 

3 REPLIES 3
SGusau
Advocate I
Advocate I

Load your data onto Power Query. If the empty cells in the Status column appear as null values skip to the next step, otherwise, right click on any empty space under the Status column and select replace value. In the dialog box that appears, leave the first box as-is and enter null in the second box, and click OK. Select the entire Status column by clicking on the header. On the ribbon, find and click Fill-->Down. You should have all values repeated as desired. 

vanessafvg
Super User
Super User

is this what you are looking for?  If so  its just a find and replace in power query.

 

vanessafvg_0-1698425931515.png

see file attached

 

1. i merged the tables in power query

2 replaced all null values with not closed





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




that's not the correct solution.  When the status changes, the new status value is repeated.  Look at 2nd image, and read the 2nd requirement.  

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.