Hi, i need the following:
I have a column named "Account" and another one named "Company" on my table and i also have a file with a table that shows the accounting format for that Company, two columns named "Account" that is a code number and "Name of the account". So i need to join the info from column "Account" with the additional file, in order to show the name of the account, right now its simple, issue comes when i deal with different companies, i have one external file for each company coding system (Each company has different accounting system), so this nested join depends on the value on company column.
Account (Number) Company (Name) Nested join (Name of the account on each company)
505123 ABAD General expenses(Info brought from additional file (one file for each company))
505123 DYNAMIC Other Incomes (Info brought from additional file with the company codes)
That is the desirable result, cause account numbers, doesnt mean the same in all companies, so i want to automatize this, right now only solution i found its to change manually in the code editor the query name of each accountable system and do the job separately by companies.
The external file data structure its simple: This should be the one for company ABAD.
Acount (Number) Name of the account (String)
505123 General Expenses
So i need to evaluate column company, depending on this, go to that file and do the nested join, thing is i cant get the origin of external file a variable and use it in the advanced editor. i tried couple solutions but no success. Anyone can help me?
Would be nice to learn how to solve this.
Actual code that it works statically, i have to change manually the PUC_XXX, i want to make this query name variable, in order that depends on the value in [Empresa] field.
#"Consultas combinadas" = Table.NestedJoin(#"Acomodar jerarquía cuentas",{"CUENTA"},PUC_DYNAMIC ,"Consulta (2)",JoinKind.LeftOuter),
Desired code, should be something like this:
#"Consultas combinadas" = Table.NestedJoin(#"Acomodar jerarquía cuentas",{"CUENTA"},if [Empresa]="DYNAMIC" then PUC_DYNAMIC else if [Empresa]="ABAD" then PUC_ABAD else PUC_TT,{"CUENTA"},"Consulta (2)",JoinKind.LeftOuter),
but this code doesnt work, it says "unknown identifier".
Hi Faiber,
You should try the Expression.Evaluate function to call different queries:
QName="Query1",
#"Consultas combinadas" = Table.NestedJoin(#"Acomodar jerarquía cuentas",{"CUENTA"},Expression.Evaluate(QueryName,[QueryName=QName]),{"CUENTA"},"Consulta (2)",JoinKind.LeftOuter),
Using Query name in Evaluate returns that query and it will be seen as a table or whatever that query results into.
.NestedJoin is not looking at specific rows context, it just takes 2 tables and joins them, you cannot use the Empressa column inside this function to loop through rows, you need an iterator for that.
I think you have to create parameters for each query name, by filtering the table based on Empressa column :
TblDynamic = Table.SelectRows(#"Acomodar jerarquía cuentas", each ([Empressa] = "DYNAMIC")) ,
TblABAD = Table.SelectRows(#"Acomodar jerarquía cuentas", each ([Empressa] = "ABAD")) ,
JoinDynamic = Table.NestedJoin(#"Acomodar jerarquía cuentas",{"CUENTA"},TblDynamic,{"CUENTA"},"Consulta (2)",JoinKind.LeftOuter),
JoinABAD = Table.NestedJoin(#"Acomodar jerarquía cuentas",{"CUENTA"},TblABAD,{"CUENTA"},"Consulta (2)",JoinKind.LeftOuter),
Combined=Table.Combine(JoinDynamic,JoinABAD)
Or, you can do that in 1 step:
Combined=Table.Combine(Table.NestedJoin(#"Acomodar jerarquía cuentas",{"CUENTA"},TblDynamic,{"CUENTA"},"Consulta (2)",JoinKind.LeftOuter) , Table.NestedJoin(#"Acomodar jerarquía cuentas",{"CUENTA"},TblABAD,{"CUENTA"},"Consulta (2)",JoinKind.LeftOuter))
