Forum Discussion

drogzy's avatar
drogzy
Icon for Helper I rankHelper I
4 years ago
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...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc4xCoAwDIXhq0jmQpNXa9TZW0gHBzdH74/FoLRSx/zkI1lXEoQ+kqPeC3sw0I0zswVYELay7Me55YmSqxy+LrbccLOMBs0teIzvlsRmqe4VUG1NZ0yt8HNOny9DrQQlU0rpAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t, #"End " = _t, Rig = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start", type datetime}, {"End ", type datetime}, {"Rig", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Min(Table.SelectRows(#"Added Index", (x)=>x[ID]=[ID])[Start])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "A Job", each try if [ID]=#"Added Index"[ID]{[Index]-1} then null else [Custom] otherwise [Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"})
in
    #"Removed Columns"

 

drogzy's avatar
drogzy
Icon for Helper I rankHelper I
4 years ago

Vijay_A_Verma 

Also, wondering how you turned my table into a text code where it starts with dc4....

 

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc4xCoAwDIXhq0jmQpNXa9TZW0gHBzdH74/FoLRSx/zkI1lXEoQ+kqPeC3sw0I0zswVYELay7Me55YmSqxy+LrbccLOMBs0teIzvlsRmqe4VUG1NZ0yt8HNOny9DrQQlU0rpAg==", BinaryEncoding.Base64), Compression.Deflate))