Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
Date | Index | State | NewColumn |
05/03/2022 | 1 | False | null |
08/03/2022 | 2 | True | null |
11/02/2022 | 3 | False | null |
08/01/2022 | 4 | False | null |
11/04/2022 | 5 | True | 11/04/2022 |
10/04/2022 | 6 | False | null |
I hope I am clear on my request, it may have some language mistakes, sorry about that.
And thanks for your help 🙂
Solved! Go to 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:
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"
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 :
Every "subgroup" table is like the one you worked on :
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:
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 😄
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)
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
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
Date | Index | State | NewColumn (expected result) |
05/03/2022 | 1 | FALSE | null |
08/03/2022 | 2 | TRUE | null |
11/02/2022 | 3 | FALSE | null |
08/01/2022 | 4 | FALSE | null |
07/02/2022 | 5 | TRUE | 07/02/2022 |
10/04/2022 | 6 | FALSE | null |
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |