Forum Discussion

drogzy's avatar
drogzy
Helper 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
Most Valuable Professional
4 years ago

Let's use your code after making connection to SQL Server is following

let
    Source = Sql.Databases("XXXXXXX\SQLEXPRESS"),
    Sample = Source{[Name="Sample"]}[Data],
    dbo_Sales = Sample{[Schema="dbo",Item="Sales"]}[Data]
in
    dbo_Sales

Remove last 2 lines from here so you are left with only this

let
    Source = Sql.Databases("XXXXXXX\SQLEXPRESS"),
    Sample = Source{[Name="Sample"]}[Data],
    dbo_Sales = Sample{[Schema="dbo",Item="Sales"]}[Data]

This is equivalent to Source statement when your import from SQL server. Some sources generate a single line and here SQL server has generated 3 lines for Source.

Now, you can copy my code after Changed Type and the code will become. If you need to Changed Type, do it after dbo_Sales. 

let
    Source = Sql.Databases("XXXXXXX\SQLEXPRESS"),
    Sample = Source{[Name="Sample"]}[Data],
    dbo_Sales = Sample{[Schema="dbo",Item="Sales"]}[Data],
    #"Added Index" = Table.AddIndexColumn(dbo_Sales, "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
Helper I
4 years ago

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? 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    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"

     

    • drogzy's avatar
      drogzy
      Helper I
      4 years ago

      Amazing! Thank you 🙂