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 

 

IDStartEnd RigA Job
123452022-04-10 8:00 AM2022-04-12 10:00 AMDelta 102022-04-10 8:00 AM
123452022-04-12 8:00 AM2022-04-15 10:00 AMDelta 6null

 

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,

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"

     

9 Replies

  • 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

      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's avatar
        Vijay_A_Verma
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

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