Forum Discussion
Solved
Creating a custom column that returns max value of another column in the same table
Hi all, Here is what I have and what I am trying to do is labeled in bold ID Start End Rig A Job 12345 2022-04-10 8:00 AM 2022-04-12 10:00 AM Delta 10 2022-04-10 8:00 AM 1...
Here is an approach which doesn't break Direct Query mode. Replace first 3 lines appropriately (i.e. Source, Test, dbo_Test1).
In 4th & 5th line replace dbo_Test1.
let Source = Sql.Databases("XXXXXX\SQLEXPRESS"), Test = Source{[Name="Test"]}[Data], dbo_Test1 = Test{[Schema="dbo",Item="Test1"]}[Data], #"Grouped Rows" = Table.Group(dbo_Test1, {"ID"}, {{"A Job", each List.Min([Start]), type datetime}}), #"Merged Queries" = Table.NestedJoin(dbo_Test1, {"ID", "Start"}, #"Grouped Rows", {"ID", "A Job"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"A Job"}, {"A Job"}) in #"Expanded Grouped Rows"
Thank you, I understand now but the thing is as soon as I add an index column it breaks my DirectQuery mode forcing me to switch all tables to import mode.
Any other suggestions? maybe while adding a custom column?
Here is an approach which doesn't break Direct Query mode. Replace first 3 lines appropriately (i.e. Source, Test, dbo_Test1).
In 4th & 5th line replace dbo_Test1.
let
Source = Sql.Databases("XXXXXX\SQLEXPRESS"),
Test = Source{[Name="Test"]}[Data],
dbo_Test1 = Test{[Schema="dbo",Item="Test1"]}[Data],
#"Grouped Rows" = Table.Group(dbo_Test1, {"ID"}, {{"A Job", each List.Min([Start]), type datetime}}),
#"Merged Queries" = Table.NestedJoin(dbo_Test1, {"ID", "Start"}, #"Grouped Rows", {"ID", "A Job"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"A Job"}, {"A Job"})
in
#"Expanded Grouped Rows"
Amazing! Thank you 🙂