Create new table by selecting specific columns and filter.

[zahidah_mabd]

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?

[zahidah_mabd · Accepted solution]

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]

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

[zahidah_mabd]

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

this is the desired output:

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.