Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
zahidah_mabd
Helper I
Helper I

Create new table by selecting specific columns and filter.

Hello, 

 

I have a problem with creating new tables from different existing tables.

So I have 2 tables 'Position' and 'Employee' which are connected thru [Staff ID] column. The relation is many-to-many.

Basically, I want to create a new table of 'Mismatched Title/Gender' where we can see the report that contains certain data from both tables.

I have created a column measure for [Title vs Gender] in 'Employee' table to identify whether the value is valid or not for employee title (e.g: Mr, Mrs, Ms) with their respective gender.

Title vs Gender = Switch(true(),

'Flexi'[Title] in { "Encik", "Mr." } && 'Flexi'[Person Gender] = "Male", "Valid Male",

'Flexi'[Title] in { "Cik" , "Miss" , "Mrs." , "Ms." , "Puan" } && 'Flexi'[Person Gender] ="Female","Valid Female",

"Invalid")


So now I want extract the data from table 'Position' and 'Employee' based on the "Invalid" results of column [Title vs Gender]

so how can I do that? 
From table 'Position' , I want extract [Position ID] and [Position Name] wheras in
table 'Employee' , I want to extract [Staff ID] and [Staff Name]

I also want to add a new column called [Data Issue]  and label "Mismatched Title/Gender" for all rows.

can you help me?





1 ACCEPTED SOLUTION
zahidah_mabd
Helper I
Helper I

So far, the only solution I can find is to merge the two tables in the power query into a new combined table and then I expand the columns that I want from the 2nd table. 

View solution in original post

3 REPLIES 3
zahidah_mabd
Helper I
Helper I

So far, the only solution I can find is to merge the two tables in the power query into a new combined table and then I expand the columns that I want from the 2nd table. 

v-chenwuz-msft
Community Support
Community Support

Hi @zahidah_mabd ,

 

Can you share your pbix file without sensitive data and expect reseult even if they are hand-drawn?

 

Best Regards

Community Support Team _ chenwu zhu

 

This is my pbix file
https://www.dropbox.com/s/iy4uczif6jdrcr0/Test.pbix?dl=0

 

this is the desired output:

zahidah_mabd_0-1655687381468.png



So basically there will be more types of data issue like missing Location etc and I will create a new separate table for each data issue with the same column fileds as the one above. and then I wil combine all data issue datables into a big table using UNION function.



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors