Results 1 to 4 of 4

Thread: Power Query - Pull Over Latest Date From Another Column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query - Pull Over Latest Date From Another Column

    This would seemingly be simple to do. I have a "Report Date"column with hundreds of rows of dates. I need a custom column that indicates with an "x" or a flag of some type if each row in the Report Date column contains the latest report date within the column itself. Not today's date but the latest date found in the column. I can do this in Excel but run into trouble trying to write it in power query. Any ideas?

    Order Report Date Latest Date
    12123 10/10/2019
    12123 10/11/2019
    12123 10/14/2019
    12123 10/15/2019
    12123 10/17/2019
    12123 10/18/2019
    12123 10/21/2019
    12123 10/22/2019 x
    12444 10/10/2019
    12444 10/11/2019
    12444 10/14/2019
    12444 10/15/2019
    12444 10/17/2019
    15555 10/10/2019
    15555 10/11/2019
    15555 10/14/2019
    32323 10/10/2019
    32323 10/11/2019
    32323 10/14/2019
    32323 10/15/2019
    32323 10/17/2019
    32323 10/18/2019
    32323 10/21/2019
    32323 10/22/2019 x

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,054
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query - Pull Over Latest Date From Another Column

    you can try with Group and Max from Report Date
    or
    Table.Max from Report Date

    but this is not an Excel worksheet way
    Last edited by sandy666; Oct 22nd, 2019 at 02:31 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,054
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query - Pull Over Latest Date From Another Column

    is this acceptable?

    Order Report Date Latest Date
    12123
    10/10/2019
    12123
    11/10/2019
    12127
    14/10/2019
    12123
    15/10/2019
    12123
    17/10/2019
    12123
    18/10/2019
    12123
    21/10/2019
    12123
    22/10/2019
    22/10/2019
    12444
    10/10/2019
    12444
    11/10/2019
    12444
    14/10/2019
    12444
    15/10/2019
    12444
    17/10/2019
    15555
    10/10/2019
    15555
    11/10/2019
    15555
    14/10/2019
    32323
    10/10/2019
    32323
    11/10/2019
    32323
    14/10/2019
    32323
    15/10/2019
    32323
    17/10/2019
    32323
    18/10/2019
    32323
    21/10/2019
    32323
    22/10/2019
    22/10/2019


    Code:
    // TableMaxDate
    let
        Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
        Tmax = Table.PromoteHeaders(Table.SelectColumns(Table.Transpose(Record.ToTable(Table.Max(Source,"Report Date"))),{"Column2"}), [PromoteAllScalars=true]),
        Merge = Table.NestedJoin(Source,{"Report Date"},Table.RenameColumns(Tmax,{{"Report Date", "Latest Date"}}),{"Latest Date"},"Latest Date",JoinKind.LeftOuter),
        Type = Table.TransformColumnTypes(Table.ExpandTableColumn(Merge, "Latest Date", {"Latest Date"}, {"Latest Date"}),{{"Report Date", type date}, {"Latest Date", type date}})
    in
        Type
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,054
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query - Pull Over Latest Date From Another Column

    or even this

    Code:
    // TableMaxDate
    let
        Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
        TMax = Table.PromoteHeaders(Table.SelectColumns(Table.Transpose(Record.ToTable(Table.Max(Source,"Report Date"))),{"Column2"}), [PromoteAllScalars=true]),
        Result = Table.ExpandTableColumn(Table.NestedJoin(Source,{"Report Date"},TMax,{"Report Date"},"Latest Date",JoinKind.LeftOuter), "Latest Date", {"Report Date"}, {"Latest Date"}),
        Type = Table.TransformColumnTypes(Result,{{"Report Date", type date}, {"Latest Date", type date}})
    in
        Type
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •