Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Unais T K]

Team,

Below Screeen Shot , Two table ” invoice Line item ” ” invoice line item sales Emp” , how can we add Employee in Invoice line item 

——————————
Unais T K
DC ADministrator
——————————

[Herbert Chitate]

Hi Unias,

You can use power query to perform a sort of look up function use the merge function but you need to create the column to link the 2 tables first as follows:

1) Create a new column in both tables which concatenates Invoice no and line item (do in both tables) for example

 =Text.From([Inv No])&” – “&Text.From([Line Item])

2) Go to Invoice line item table and in power query merge (home tab top right hand corner) to employee table based on the new column created.
3) During the merge, drop all other columns and leave Sales Employee only from the 

See attached worked example.

Hope this helps

Regards

——————————
Herbert Chitate
BI Reporting Consultant
——————————
——————————————-

[Unais T K]

hI HERBERT,

THANKS FOR YOUR REPLAY..
CAN YOU LOOK BELOW, I GOT A SENARIO SPECIFIED IN INVOICE 3 , DONT HAVE INDVIDUAL VALUE IN SECOND TABLE BUT NEED TO EACH LINE ITEM SAME SALES EMPLOYE

——————————
Unais T K
DC ADministrator
——————————
——————————————-

[Herbert Chitate]

Hi Unais,

You need top consider how an employee is uniquely identified. I notice that you have added another column called TYPE. Is the unique identifier for an employee “Inv No + Line Item + TYPE”?

If this is the case, then during data import I would add a default value where you have blanks, because you cannot have blanks in a look up table like employees.

I am assuming that you do not wish to drop an rows with blanks

Hope this helps

Herbert

——————————
Herbert Chitate
Finance Manager
——————————
——————————————-

[Vishesh Jain]

Hi ?,

I completely agree with , that you need to have some sort of ‘key column’ in both your tables.
There is no unique identifier in your fact table as even the Inv Numbers are being repeated.

Any sort of merge or relationship will need some sort of unique values.

You could try replacing the blank values in your Line Item column with something, so that a key is created.

Just a word of caution that if you create a key column using the values from the Type column, it might not work, since your fact table does not have a Type column.

Hope this helps.

Thank you,
?

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Unais T K]

Team,
Am manage to fina solution,
I had created two table invoice line item with emp in one table and invoice without in another table one table relation made with combination invoice line item and another table made only with invoice no.
Then invoice table created 3 colum one related with invoice with line item emp table and another column used invoice without line item emp then 3rd column used is blank function of first two column

——————————
Unais T K
DC ADministrator
——————————
——————————————-