Table Nested Joint - where clause

[patator35]

Hello

I'm builing up a summary table of sales targets and past year sales.

I would like to add a column to my table containing sales of a pre-set year (ie 2015).

In the following  formula, I would like to add a "where" clause :

= Table.NestedJoin(#"Valeur remplacée",{"ID_Target"},Targets_Data_Year,{"ID_Target"},"Targets_Data_Year",JoinKind.LeftOuter,)

..where Targets_Data_Year.Year="2015"

How can I do that ?

thanks for your help

[patator35]

Hello again

I'm really stuck and would appreicate any kind of support helping me to advance.

Maybe my question was not clear, please let me know if this is the case...

thanks

[ricardocamargos]

Hi @patator35

Why don't you filter the table before ? 

Ricardo

[patator35]

Ricardo

thanks for your support.  

2 reasons :

- i use the year table for other queries and don t want to prefilter it

- I will use this "year sales" table to add 3 columns in the "target" table (containing the nested join fonction) : 2015 sales | 2016 sales | 2017 sales

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[patator35]

Table.NestedJoin(#"Valeur remplacée",{"ID_Target"},Targets_Data_Year,{"ID_Target"},"Targets_Data_Year",JoinKind.LeftOuter)

= Table.ExpandTableColumn(#"Requêtes fusionnées3", "Targets_Data_Year", {"Year", "Qty"}, {"Targets_Data_Year.Year", "Targets_Data_Year.Qty"})

Here is the final table that will be loaded in Excel (and will be used as source for a pivot table) :

Here is the table containing sales per year ("Targets_Data_Year"):

Here is the table containing sales targets ("Targets") - I want to add column 2014,2015,2016... (containing qty/sales of the above table)

[Anonymous]

Hello,

have you managed to find a solution?

Something like this would benefit me so much.

[ricardocamargos]

@patator35,

Doesn`t it work if you just create a filtered copy (you don`t need to filter the base table) ?

Ricardo