Linking to a single name table from 4 different tables

[Phyxius]

Hi.

I have data in four different spreadsheets which represents the Accounts Payable Transaction History of 4 separate financial systems. In each financial system, the business refers to exactly the same vendor but often with a different name. I need to map all names for the same entity from these four separate records to a single name, within a separate table which I can then use in a PowerBi query.

I'm a new user, self teaching and learning as I go. I have been advised that Merge, Append and the creation of a "Link Table" with a unique key (value/identifier) are essential but do not know where to start.  Can the community please help? Thanks in advance.

[v-yulgu-msft]

Hi @Phyxius,

The "Vendor Link Table" is your desired result which should be generated in Power BI right? Is there any relationship about vendor names in these 4 tables? Apart from these 4 financial tables, is there any other source that can determine the mapping relationship of vendor names? If all these tables are completely separate, there is no way to achieve above requirement in Power BI. You have to generate the Link table in your source data to list all mapping relationships.

Regards,

Yuliana Gu

[Phyxius · Accepted solution]

Hi Yuliana, thanks for the post.

Correct, I was hoping to somehow create a link table in PowerBI and then use that within a larger spend analysis query. The spend data attached to each table record of a vendor would then carry through to the vendor record in the link table, irrespective as to the financial system from which it originated.

The only relationship between names is text. I am unable to obtain a DUNS, Tax or company record number for the entities to act as a unique key.