related max date and no related records

[kintera]

I have relationships defined as follows:

With example data (renaming my tables to make them a little easier to work with).

The table on the left above is table A (formerly ProgramByObligationDetailBasic), the table on the middle above is table B (formerly tjxProgByOblDet_AddlJobData) , and the table on the right above is table C (formerly tblPropAddlJobData).

table A has one to many relationship with table B.

Table B has many to one relationship with table C.

This can be seen by looking at the sample data below

Table A
pktblA_ID Job tblA    ModDate
1               45678        1/1/2019
2               56789       1/2/2019
3               67891       1/10/2019
4               43234       1/20/2019

Table B
fktblA_ID     fktblC_ID   tblBModDate
1                 10              1/1/2019
2                 10              1/2/2019
2                 11              1/3/2019
3                 11              1/10/2019
3                 12              1/15/2019


Table C
pktblC_ID          Money     tblCModDate
10                      5               1/1/2019
11                      7              1/3/2019
12                      8              1/15/2019

I want the user to be able to filter from table A. When filtering from table A, I want to show a value of 0 for Money if there is no related value in table C, and only show the records from table C with the Max date. I have tried many things (grouping, etc) and hav failed.

If the user selects pktblA_ID = 2, it would return pktblC_ID = 10 AND pktblC_ID = 11 (as there are 2 records in tblB for  pktblA_ID = 2).

What I want is only the most recent record in tblC (or pktblC_ID = 11 with tblCModDate = 1/3/2019).

For pktblA_ID=4, there is no related records, so I want to return 0.

Thank you for any help.

Lex

[Anonymous]

try these, but pay close attention to the types of variable columns:

query A

let
    #"Modificato tipo" = Table.TransformColumnTypes(tabA_Table,{{"pktblA", Int64.Type}, {"Job_tblA ", Int64.Type}, {"ModDate", type any}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "recC",  (rA)=> try Table.SelectRows(tabB, (rB)=> rA[pktblA]=rB[fktblA_ID])[recC]{0} otherwise 0)
in
    #"Aggiunta colonna personalizzata"

query B

let
    #"Modificato tipo" = Table.TransformColumnTypes(tabB_Table,{{"fktblC_ID", type number}, {" tblBModDate", type date}, {"fktblA_ID", Int64.Type}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"fktblA_ID"}, {{"C_ID", each _[fktblC_ID]}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Raggruppate righe", "recC", (rB)=>Table.Max(Table.SelectRows(tabC, each List.Contains(rB[C_ID],_[pktblC_ID])),"tblCModDate"))
in
    #"Aggiunta colonna personalizzata"

Let me know.

[kintera]

Rocco_sprmnt21, thank you for the response. Unfortunately, I don't know how to read M Code. Can you explain this in another way?

[Anonymous]

I'm not sure how to explain the meaning of the code if you know little about M.
I try with figures, let's see if it is sufficient to give an idea of the attempted solution.
But what exactly do you expect about this?

First you need to load the three tables into power queries.

For simplicity, let's call them tabA, tabB and tabC

Query B:

this part of code group the row with the samo ID in tabB:

let
    #"Modificato tipo" = Table.TransformColumnTypes(tabB_Table,{{"fktblC_ID", type number}, {" tblBModDate", type date}, {"fktblA_ID", Int64.Type}}),

    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"fktblA_ID"}, {{"C_ID", each _[fktblC_ID]}}),

the rest of code of query B, get the info requested from tabC:

    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Raggruppate righe", "recC", (rB)=>Table.Max(Table.SelectRows(tabC, each List.Contains(rB[C_ID],_[pktblC_ID])),"tblCModDate")),
    #"Tabella recC espansa" = Table.ExpandRecordColumn(#"Aggiunta colonna personalizzata", "recC", {"Money", "tblCModDate"}, {"Money", "tblCModDate"})
in
    #"Tabella recC espansa"

similarly for query A, get the tables containing the info of tabC that now are in tabB:

let

        #"Modificato tipo" = Table.TransformColumnTypes(tabA_Table,{{"pktblA", Int64.Type}, {"Job_tblA ", Int64.Type}, {"ModDate", type any}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "recCC",  (rA)=> try Table.SelectRows(tabB, (rB)=> rA[pktblA]=rB[fktblA_ID])otherwise 0),

 

finally, only the info of interest is exposed

    #"Tabella recCC espansa" = Table.ExpandTableColumn(#"Aggiunta colonna personalizzata", "recCC", {"Money", "tblCModDate"}, {"Money", "tblCModDate"})
in
    #"Tabella recCC espansa"

[kintera]

It appears in the first step "Query B", you are Grouping on a column within table B. Are you hand-typing? Or clicking Transform>Group_By? I *believe* you have to have Aggregation (ie Sum, Average, etc), but don't see that code. In the end, I want to see one record for each table A, and the corresponding (most recent Mod Date) Money from table C. In the last picture, I see "C_ID", which I believe is the key from table C that you renamed (or aliased). Is that right? Again, I don't know how you got the code. Can you explain that?

thank you

[Anonymous]

I usually use the GUI, but in some more complex situation I have to hand modify/adapt the code. I'm also new/entusiast on M/power query. I'a have (as you can see) some difficulty to write in english, so I can't explain in more detail the concept. I' sorry.