Problem: FILTER AddColums Lookup

[jup]

Hi again:

this formula works fine:

this formula drops an error - but why?:

I've just added the "filter".

Thanks

jup

[RandyHayes]

@jup 

Does Filter('[dbo].[Data]'; reflex="12345") by itself return anything?

[jup]

Yes - 

Filter('[dbo].[Data]'; reflex="12345")

is working fine.

[v-xida-msft]

Hi @jup ,

Could you please share a bit more about the error message with your formula?

For your first formula, the AddColumns function is applied to your SQL Table data source ('[dbo].[Data]') itself, so you could reference values from '[dbo].[Data]' table directly.

But within your second formula, the AddColumns function is applied to the Filter formula, so you could not reference values from your '[dbo].[Data]' table directly, you could reference values from the result the Filter formula returns.

In order to fix your issue, I think RenameColumns function could achieve your needs. Please consider modify your formula as below:

AddColumns(
             RenameColumns(Filter('[dbo].[Data]'; reflex="12345"); "EJ_UID", "EJ_UID1");
             "cust_Tor";
             LookUp('[dbo].[Data_Custom]'; EJ_UID = EJ_UID1).Tor
)

Please take a try with above formula, then check if the issue is solved.

More details about RenameColumns function, please check the following article:

RenameColumns function

Best regards,

[jup]

Great! It works 😄

But if I want to have more fields... is this the right way?? (repeat the lookup part again and again for every field I need):

AddColumns(
             RenameColumns(Filter('[dbo].[Data]'; reflex="19-1286.02"); "EJ_UID"; "EJ_UID1");
             "cust_Tor";
             LookUp('[dbo].[Data_Custom]'; EJ_UID = EJ_UID1).Tor;
             "cust_Crew";
             LookUp('[dbo].[Data_Custom]'; EJ_UID = EJ_UID1).Crew;
             "cust_Ladezeit_Lager";
             LookUp('[dbo].[Data_Custom]'; EJ_UID = EJ_UID1).Ladezeit_Lager;
             "cust_Status";
             LookUp('[dbo].[Data_Custom]'; EJ_UID = EJ_UID1).Status            
             
)

Thanks

jup

[v-xida-msft · Accepted solution]

Hi @jup ,

Yeah, you are right.

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,