Lookupvalue for multiple tables

[Anonymous]

Hello everyone,

I'm a dynamics 365 junior developer, starting out with learning Business Central and AL and now I want to get into Power BI.

I'll get right into my problem, sorry if I make any mistakes.

I've got multiple tables from Business Central, among them are these : GL Entries, Vendors, Customers & Bank Accounts.

GL Entry table has a Source No. that can be completed with the code of any of the Vendor, Customer or Bank Account tables.

In my GL Entry table I want to create a new column Source Name that contains the name column from every of those other tables ( Vendor, Customer, Bank Account ).

Here's an example from the Customer table, my Source No. references the No column but I want to retrieve the Name column as the value instead.

From what I know, for one column this is very easy to do with a LOOKUPVALUE that retrieves the column desired, in my case on the other hand Source No. from GL Entry is completed from 3 different tables.

How can I create a new column in GL Entry that can retrieve the Name column from 3 different tables?

I would really appreciate the help!

Also, I've already linked the GL Entry Source No. to every other No. column of the other 3 tables. I'm not sure if that is needed.

Thank you for your time! Have a great day!

[v-xulin-mstf · Accepted solution]

Hi @Anonymous

You can combine columns from three different tables into one dimensional table.

Please refer:https://docs.microsoft.com/en-us/dax/union-function-dax

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Sorry for the late reply.

I've managed to solve this problem trough 3 table merges, expanding only the name column for each one then once again merging all the 3 columns together.

Thanks for the response, I'll check the UNION function.

Have a great day!