Creating a custom column that returns max value of another column in the same table

[drogzy]

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
12345	2022-04-12 8:00 AM	2022-04-15 10:00 AM	Delta 6	null

Basically I want the  A Job column to return the minimum value of the Start column. Now, I cannot do group by because of my direct query to an sql database (it breaks) so I was hoping for some help on how to write the code in power quey to create such a column. 

Thank you,

[Vijay_A_Verma]

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]

@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))

[drogzy]

Hi @Vijay_A_Verma , 

Thanks a lot for this detailed explanation. I am not familiar with advanced editor so while I am trying this I have a few questions. 

I put your code in the editor and added a few more columns I have under the source row and the changed type row (followed same approach you did by labeling them as text). Now it gives me an error because it can't find the additional columns I added in there.

How can I reference my actual table in the advanced editor like you have done or better yet How can I use the query on my dataset?

[Vijay_A_Verma]

In Power Query UI, in Home tab on extreme right, you will see Enter Data. Here, you can enter data manually rather than fetching from a Source. This will generate that type of code for the Source which you are using. 

You would need to input your data using Enter data or get data from somewhere. If you use Enter data, you can't edit it to add or delete or update. Once you do it, you will get a source line in your Advanced editor. Now copy this source line and replace my source line in my code.

[drogzy]

The table I am trying to make these changes to is a direct query to an sql database (it updates in real time).  I am a bit confused as to how will this separate manually entered table in a blank query make changes to my real table. Am I able to connect the two? 

[Vijay_A_Verma]

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]

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 · Accepted solution]

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]

Amazing! Thank you 🙂