topic Re: Unable to join two tables based on condition Power Query in Power Query

Unable to join two tables based on condition Power Query

AsmaBenhassine9 — Sat, 31 Oct 2020 21:58:42 GMT

I have Table A and Table B and i need to get the Tranche_ID from Table B.

Table A

ID_Client	ID_Collaborateur	Nombre_Dossiers
1	2	6
1	3	4
2	3	2

Table B

ID_Tranche	Min_Tranche	Max_Tranche
1	1	2
2	3	5
3	6	10

Condition : if Nombre_dossiesr > Min_tranche and Nombre_dossiers < Max_Tranche then => Recover the ID_TRANCHE

Result

ID_Client	ID_collaborateur	ID_Tranche	Nombre_dossiers
1	2	3	6
1	3	2	4
2	3	1	2

i create a custom column in power query :

Table.SelectRows(TableB, (TableB) => TableB[Min_Tranche]<=[Nombre_dossiers] and TableB[Max_Tranche] >= [Nombre_dossiers])[ID_Tranche]{0}

but i have this error

Any help please ? !

Re: Unable to join two tables based on condition Power Query

shaowu459 — Sun, 01 Nov 2020 02:39:04 GMT

Hi AsmaBenhassine9, try below code:

let tblA = Excel.CurrentWorkbook(){[Name="TableA"]}[Content], tblB = Excel.CurrentWorkbook(){[Name="TableB"]}[Content], res = Table.AddColumn(tblA," ID_Tranche",each Table.SelectRows(tblB,(x)=>x[Min_Tranche]<=[Nombre_Dossiers] and x[Max_Tranche]>=[Nombre_Dossiers]){0}[ID_Tranche]) in res

Re: Unable to join two tables based on condition Power Query

CNENFRNL — Sun, 01 Nov 2020 02:51:52 GMT

Salut, AsmaBenhassine9 , ta formule marche bien de mon côté 😂; la formule complete est comme ça

#"Colonne Ajoutée" = Table.AddColumn(#"Renamed Columns", "ID_Tranche", each Table.SelectRows(TableB, (t) => t[Min_Tranche]<=[Nombre_dossiers] and t[Max_Tranche] >= [Nombre_dossiers])[ID_Tranche]{0})

Re: Unable to join two tables based on condition Power Query

AsmaBenhassine9 — Sun, 01 Nov 2020 12:55:15 GMT

it works very well. Thank you 🙂