Vendor report including GL cost code

[MC13]

I am trying to write a report in Power BI that lists Vendor activity but also includes the GL codes the invoices have been posted to.  I have managed to create a report but it only shows the GL as accounts payable.  When I look at this it is because the purchase invoice lines entries are not recorded in the Vendor Ledger Entries table.  They are in the General ledger entries table and I have tried to find a way of linking the General Ledger Entries to the Vendor but without success. 

Does anyone know how I can do this? 

[Anonymous]

Hi @MC13 ,

Not very clearly...... Could you  make up some fictitious data based on the columns in the table and the relationship between the tables and describe the expected output?

Best regards, 

Eyelyn Qin 

[amitchandak]

@MC13 ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[MC13]

I am not sure how to send it to you removing sensitive data.  Basically I am pulling through into Power BI the General Ledger Entries Table and the VendorLedgerEntries table.  I have also tried the Vendor_Ledger_Entries table.  

The VendorLedgerEntries table do not include the cost posting lines from the invoice by the looks of it just the accounts payable line.  I have compared the entries with the General Ledger Entries table whch has both.  The tables are linked by enry number so as the entry no line with the cost code is not in the VendorLedgerEntries table it does not show the vendor name.  If I try and llnk by say document number this works but it is a many to many link and then values get lumped together so I do not see the correct value.

I think my problem is undestanding how to link the correct tables together to show the vendor name.  I don't undesrtand why I cannot pull through a table with the purchase invoice lines showing the vendor.