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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Grogu69
Frequent Visitor

Return last specific value on a column based on a max index in another column

Hi everyone,

 

I'm asking your help please, I don't find how to get my request done, even if it seems simple :

Here is an exemple table, with explanations (what I want to do is the [NewColumn]) :

 

In the [NewColumn], I want to return the column [Date] value, only for the last value "True" in the column [State], based on the [Index] column

So in this exemple, the last row (based on [Index]) where [State] is "True" is the fifth, so in my [newColumn], only the [Date] of this row should be returned. Others are "null"

 

DateIndexStateNewColumn
05/03/2022   1Falsenull
08/03/2022   2Truenull
11/02/2022   3Falsenull
08/01/2022   4Falsenull
11/04/2022   5True11/04/2022
10/04/2022   6Falsenull

 

I hope I am clear on my request, it may have some language mistakes, sorry about that.

And thanks for your help 🙂

1 ACCEPTED SOLUTION

Hey, man @Grogu69 
As long as you "repay" with Kudos, I am here 😄 (Just kidding, no problem):
It's not the cleanest, but it will do the job:

vojtechsima_0-1649865466384.png

 


Paste this to "Blank Query" and you will get the result and you can check all the steps:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc49CsAgDIbhuzgXSaIWV5V26tSfqXj/a1ShUiE1gzi8fOG5bwVOg9EERGpSWN4atmMpf1B5Ktl3ub5zv76KqIFaNb9jbNmyXNe2ZcduQ1dnfnvkjqI7yu4ou981dTIEdnwEjzI8ifAkw5MMf9emk3l2e+QuOT8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t, State = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Temporary", each Table.RowCount(_), Int64.Type}, {"Table_Column", each _, type table [Date=nullable date, Index=nullable text, State=nullable text, Category=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "HighestIndex", each let 
TrueIndex = Table.SelectRows([Table_Column],each [State] = "TRUE"),
HighestRecord = Table.Max(Table.TransformColumnTypes(TrueIndex, {{"Index", type number}}), ("Index")),
HighestIndex = HighestRecord[Index]

in
HighestIndex),
    #"Expanded Table_Column" = Table.ExpandTableColumn(#"Added Custom", "Table_Column", {"Date", "Index", "State"}, {"Date", "Index", "State"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Table_Column",{{"HighestIndex", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "TheDate", each if [Index] = [HighestIndex] then [Date] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"TheDate", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Temporary"})
in
    #"Removed Columns"

 

 

View solution in original post

8 REPLIES 8
Grogu69
Frequent Visitor

Perfect, it's exactly what I wanted, thanks you !

 

However I thought I'd be able to incorporate the solution in my file, but it seems I overestimated me.... So if I can abuse of you a little again please... 😄

 

to give you a little context, I have a table with millions of Row, than I grouped by ID, like this :

Grogu69_0-1649855721393.png

 

Every "subgroup" table is like the one you worked on :

Grogu69_1-1649855965665.png

 

My goal is to apply your formula to every "subgroup" table, exactly like what does the "Table.AddIndexColumn" formula. I tried to adapt this Index formula with your code, but I'm getting nowhere. I also tried by adding new custom column but I dont understand how apply it to each subgroup...

 

Can you please (again), help me ? It should be the last time 😄 (for this problem anyway)

Hey, man @Grogu69 
As long as you "repay" with Kudos, I am here 😄 (Just kidding, no problem):
It's not the cleanest, but it will do the job:

vojtechsima_0-1649865466384.png

 


Paste this to "Blank Query" and you will get the result and you can check all the steps:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc49CsAgDIbhuzgXSaIWV5V26tSfqXj/a1ShUiE1gzi8fOG5bwVOg9EERGpSWN4atmMpf1B5Ktl3ub5zv76KqIFaNb9jbNmyXNe2ZcduQ1dnfnvkjqI7yu4ou981dTIEdnwEjzI8ifAkw5MMf9emk3l2e+QuOT8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t, State = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Temporary", each Table.RowCount(_), Int64.Type}, {"Table_Column", each _, type table [Date=nullable date, Index=nullable text, State=nullable text, Category=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "HighestIndex", each let 
TrueIndex = Table.SelectRows([Table_Column],each [State] = "TRUE"),
HighestRecord = Table.Max(Table.TransformColumnTypes(TrueIndex, {{"Index", type number}}), ("Index")),
HighestIndex = HighestRecord[Index]

in
HighestIndex),
    #"Expanded Table_Column" = Table.ExpandTableColumn(#"Added Custom", "Table_Column", {"Date", "Index", "State"}, {"Date", "Index", "State"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Table_Column",{{"HighestIndex", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "TheDate", each if [Index] = [HighestIndex] then [Date] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"TheDate", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Temporary"})
in
    #"Removed Columns"

 

 

It's just perfect, it works exactly as expected 🙂

Thanks you again for your help, really nice.

 

PS : I do the Kudos, it's the least I can do 😄

vojtechsima
Memorable Member
Memorable Member

Hi, @Grogu69 

NewColumnNew = 

var findMaxDate = MAXX(FILTER('Table', 'Table'[State]=TRUE()), 'Table'[Date])
var finditsIndex = MAXX(FILTER('Table', 'Table'[Date] = findMaxDate), 'Table'[Index])

return if('Table'[Index] = finditsIndex, findMaxDate)

vojtechsima_0-1649754256411.png

 

Thank you very much, it works very well but I'd like to have it in Power Query (M), do you think it's possible or it have to be done in DAX ?

I have multiple other transformations before and after this one and if posible I'd like to avoid switching with DAX

Hi, @Grogu69 
Sure, no problem:

let maxDate = List.Max(#"Changed Type1"[Date])
in
if [State] = true and [Date] = maxDate then maxDate  else null

vojtechsima_0-1649755866391.png

Whole query here (copy to Blank Query and you'll see)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUfLMS0mtANLBJRC+X2q5c35OaW6eUqxOtJKBqb6Bsb6RgZGRgoICUNYQiN0Sc4pBKvNKc3IgiixQFRkBcUhRKYoaQ0N9AyMkNca4DDJEUmSCTRHIJBMkRaYI2xBSEJUGqCrNMIyLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Index", Int64.Type}, {"State", type logical}, {"NewColumn", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each let maxDate = List.Max(#"Changed Type1"[Date])
in
if [State] = true and [Date] = maxDate then maxDate  else null),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
    #"Changed Type2"

 

Thanks but I think it's not responding to the need with this solution because if I understand it well, it's taking the highest date in the Date Column and put it in the row if "State" is True.

But what I need is to put the date value corresponding to the max "index" having "State" = True

 

In the exemple it works because the max date is also the one being in fifth row, but it's coincidence. If I changed the date, it's not working anymore.

 

In this table, State is TRUE on rows two (index 2) and five (index 5). 5 is higher than 2, so I want that it's the only row where the "Date" value is reported in the NewColumn

Thank you again for your help 🙂, sorry if I'm not easily readable

 

DateIndexStateNewColumn (expected result)
05/03/2022  1FALSEnull
08/03/2022  2TRUEnull
11/02/2022  3FALSEnull
08/01/2022  4FALSEnull
07/02/2022  5TRUE07/02/2022
10/04/2022  6FALSEnull

@Grogu69 
I see, it's clear now, here is the code:

let 
TrueIndex = Table.SelectRows(#"Changed Type1",each [State] = true),
HighestRecord = Table.Max(TrueIndex, "Index"),
HighestIndex = HighestRecord[Index]

in
if [Index] = HighestIndex then [Date] else null

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.