Forum Discussion
Unable to join two tables based on condition Power Query
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 ? !
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})
3 Replies
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 resSalut, 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})it works very well. Thank you 🙂
