Create New table in power BI desktop

[pabburi]

Hi All,

I have flat input data which has APPID(MANY) to join with other table having APPID (ONE) so the relation wil be ONE TO MANY kind.

Here i need to create a new table in power BI desktop for the flat input data in a format that could make ONE TO ONE realtionship with other table before building any visualization.

This new format i need to develop in query editor or Data but not using any visual in report tab.

Attaching the input and required output screenshots 

[v-xicai]

Hi pabburi,  

From your description, it looks like you already created a 1:N relationship, may I know is there any specific purpose to build a 1:1 relationship instead?  

Based on my research,  it’s not able to implement a table OUTPUT showing in your picture via query editor or Data view, to work around the issue, you can follow below steps:  

Firstly, create new Intermediate table named A with unique APP-ID value, using  DAX below:

A = DISTINCT(INPUT[APP-ID])  

Then, create new relationship between table INPUT and table A ,setting Cardinality as "Many to one" and Cross filter direction as "Both". This means for filtering purposes, both tables are treated as if they're a single table. Finally ,you can make One to one relationship using table A with other table before building any visualization.    

Attachment is my pbix test file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVU1Z7KcmzxBq9gyU7...

If you have any other issue, please feel free to ask.  

Best Regards,

Amy