cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pabburi Regular Visitor
Regular Visitor

Create New table in power BI desktop

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 

 

INPUT.PNGOUTPUT.PNG

1 REPLY 1
Community Support Team
Community Support Team

Re: Create New table in power BI desktop

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.    

 

4.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 451 members 3,807 guests
Please welcome our newest community members: