Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Last value

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

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

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]

 

9.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

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

image.png

 

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
Not applicable

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
Community Support
Community Support

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]

 

9.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I worked like a charm !!!!!

 

THANKS GUYS

artemus
Employee
Employee

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors