How to link two tables together?

[united2win]

Hi, 

Any help will be greatly appreciated. I have the following table:

Client	Service Required	Country	Status
Company A	VAT Registration	DE	Closed
Company A	VAT Registration	FR	WIP
Company A	VAT Transfer	BE	WIP
Company A	VAT Transfer	AT	Registered
Company B	VAT Registration	DE	WIP
Company B	VAT Registration	BE	WIP

I have another table with the sales bookings and the naming convention on product name does not follow a consistent pattern.

Client	Product Name	Sales Book Value
Company A	VAT Registration-DE,FR	12000
Company A	VAT Transfer-BE,FR	1100
Company B	VAT Registration-DE,BE	50000

My job is to provide a status on these bookings using the first table on a per registration level. How would you best go about this?  

[v-piga-msft]

Hi @united2win ,

If it is convenient, could you share your desired output so that we could understand your logic better?

Best Regards,

Cherry

[united2win]

Hi all,

I'm using the unpivot function as per Greg's suggestion and it's working well. Now, I have encountered another issue where I have three coloumns and I need to combine the data into one coloumn for only countries. Any suggestions? I've attached a screenshot below.

[Anonymous]

Create a table with single unique values, probably with distinct Client names.

Then, create a link between them with cardinality one to many for both tables. Use this variable as the filter or variable in the matrix, and it will work for both tables.

PBI works better doing Star Structure for Databases. Look it up and understand the logic.

You can also watch videos from this page: https://www.youtube.com/watch?v=vjBprojOCzU (this video should help you)

[Greg_Deckler · Accepted solution]

In the second table it seems like you need to split your second column on the dash. Then you are going to need to do something to get the resulting second column from that split into rows, something like an unpivot or pivot. @ImkeF should be able to help.