Forum Discussion
Help with pulling up latest non nullable value group by ID
The link for excel spreadsheet for this table is below :
This is how the table looks like in Power BI.My goal is to fill up the blank comments (where IsCurrentVersion=1) with last non-null value for the same ID.
For example, the blank comment row for ID 6355 where IsCurrentVersion=1 should be populated with “Hi, Richard is asking for accedd on the Bed Capacity Tracking” site".
For ID 6360, latest row should remain blank because there’s no previous non-null value for the same id .
For 6362, latest row should be “Sent an MN to tier 2 for the ticket”
For 6379, latest row should be “Added roles for all”
To achieve this, I tried to use the LASTNONBLANKVALUE function. My formula is here :
Column = IF (‘’Sheet1’IsCurrentVersion]=1 && ‘Sheet1’ [Comments]="", LASTNONBLANKVALUE(' Sheet1'[Last_Modified],'sheet1'[_Comments]))
But this formula won’t work because my Date column is not standard. It doesn’t have all the dates in a year.
So I tried to add a custom column
https://1drv.ms/x/s!Apg-ha77_EillQlnj2TCAhNPDhvg?e=zQ1w7Z
Then fill up
Obviously that didn’t work either because it doesn’t understand the Grouping of ID’s. For example For ID 6360, latest row should remain blank because there’s no previous non-null value for that particular ID .
Can anyone advice on groupoing/ filling down or any other custom column method?
Found the solution:
let
Source = Excel.Workbook(File.Contents("C:\Users\smoeller\Downloads\Excel Source.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"ID", Int64.Type}, {"Last_Modified", type datetime}, {"IsCurrentVersion", Int64.Type}, {"Comments", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Last_Modified", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each Table.FillUp(_, {"Comments"}), type table [Title=nullable text, ID=nullable number, Last_Modified=nullable datetime, IsCurrentVersion=nullable number, Comments=nullable text]}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Title", "ID", "Last_Modified", "IsCurrentVersion", "Comments"}, {"Count.Title", "Count.ID", "Count.Last_Modified", "Count.IsCurrentVersion", "Count.Comments"})
in
#"Expanded Count"
1 Reply
Found the solution:
let
Source = Excel.Workbook(File.Contents("C:\Users\smoeller\Downloads\Excel Source.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"ID", Int64.Type}, {"Last_Modified", type datetime}, {"IsCurrentVersion", Int64.Type}, {"Comments", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Last_Modified", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each Table.FillUp(_, {"Comments"}), type table [Title=nullable text, ID=nullable number, Last_Modified=nullable datetime, IsCurrentVersion=nullable number, Comments=nullable text]}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Title", "ID", "Last_Modified", "IsCurrentVersion", "Comments"}, {"Count.Title", "Count.ID", "Count.Last_Modified", "Count.IsCurrentVersion", "Count.Comments"})
in
#"Expanded Count"