Last value

[Anonymous]

Hi all,

here is my little issue : 

i have 2 tables :

   - the first one stores my client litigation files

   - seconde one stores my provisions : for 1 litigation file, i can store several historical provision values

I want to get the latest provision value for each litigation files.

Example :

TAB_Litig_File

File_1  |   01/01/2019

File_2  |   15/06/2019

TAB_Prov

File_1  |  01/01/2019  |  0

File_2  |  15/06/2019  |  0

File_2  |  30/06/2019  |  1000

File_2  |  10/09/2019  |  5000

I want to merge TAB_Prov into TAB_Litig_File so that :

TAB_Litig_File_Merged

File_1  |   01/01/2019  |  0

File_2  |   15/06/2019  |  5000

Thanks for your helps guys

[Anonymous]

Hi @v-lid-msft and @artemus 

and a big up to both of you

even if i can't make it with none of the two methods  🙂

I'll try to make a dummy file for each answer, it will be easier for you to help !

Thanks again for your kind help

EDIT : @v-lid-msft  i've just seen your printscreen (didn't got it from the email notif), it may help

[Jimmy801]

Hello @Anonymous 

you can merge both tables, and then apply a transformColumns to the nested table, filtering the date colum the max of date column.

Here the complete solution, containing also both tables

let
    TAB_Litig_File=
    let
        Source = #table
        (
            {"File","Date"},
            {
                {"File_1","43466"},	{"File_2","43631"}
            }
        ),
        ToDate = Table.TransformColumns
        (
            Source,
            {
                {
                    "Date",
                    each Date.From(Number.From(_)),
                    type date
                }
            }
        )
    in 
        ToDate,
    TAB_Prov = 
    let
        Source = #table
        (
            {"File","Date","Provision"},
            {
                {"File_1","43466","0"},	{"File_2","43631","0"},	{"File_2","43646","1000"},	{"File_2","43718","5000"}
            }
        ),
        ToDate = Table.TransformColumns
        (
            Source,
            {
                {
                    "Date",
                    each Date.From(Number.From(_)),
                    type date
                }
            }
        )
    in 
        ToDate,
    Join = Table.NestedJoin(TAB_Litig_File, "File", TAB_Prov, "File", "TAB_Prov"),
    SelectRows = Table.TransformColumns
    (
        Join,
        {
            {
                "TAB_Prov",
                (tableint)=>
                Table.SelectRows(tableint, each [Date]=List.Max(tableint[Date]))
            }
        }
    ),
    Expand = Table.ExpandTableColumn(SelectRows, "TAB_Prov", {"Provision"}, {"Provision"})
in
	Expand

this is the core piece... it applies the changes of nested merged table

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thanks for your answer Jimmy, really appreciate it

but i admit i'm a lazy guy and @v-lid-msft  solution has my favor  🙂

kuddos !

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can also add a custom column to meet your requirement:

let F = [File]
in
Table.Max(Table.SelectRows(#"TAB_Prov", each [File] = F),{"Date"})[Value]

Best regards,

[Anonymous]

I worked like a charm !!!!!

THANKS GUYS

[artemus]

You can  add a custom step like:

= Table.Group(Source, {"TIMESTAMP"}, {{"Row", each List.Max(_, null, each [TIMESTAMP]), type record}})

 Change Source to the name of your last step, and TIMESTAMP (both of them) to the column with the date. After this step, click the expand row button on the Row column, deselect your date column, and deselect the "Use origional name as column prefix:.

 As for merging the queries, you should be able to click the merge queries button.