Power BI Dax Create New Table From Existing Columns

Question

Newbie with DAX, trying to do something which I thought would be very simple but already hitting a bit of a wall and would appreciate any assistance.

I have table 'General' with columns:

'name', 'age', 'dob', 'address', 'home number', 'state'.

I have table 'Work' with columns:

'name','work address', 'company', 'mobile', 'email'.

I want to create a new (virtual?) table made of the following columns: 'General'[name], 'General'[age], 'General'[state], 'Work'[company], 'Work'[email]

Googling around, some of the solutions for creating a new table look really messy.. isn't there a super simple way to construct this? Thanks!

mkRabbani mkRabbani · Accepted Answer · 2020-08-03T10:12:02

There are two option, first your can use "Merge Queries" option in the "Power Query Editor". You can merge two or more table this way based on Key column available in both tables. You can also use more than one Key columns for joining purpose. You can add new columns to your left table Or, you can create a new table from those two tables which is my preference. For more details, you can check this below link-

https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query#:~:text=In%20Power%20BI%20Desktop%20you,Right%20part%20of%20the%20join).

Option 2 is to create two custom columns 'Work'[company] & 'Work'[email] in the table "General". To generate value in those columns, you can use LOOKUPVALUE. Here is a sample code for that -

company = 
LOOKUPVALUE(
    'Work'[company]
    'Work'[id], 'General'[id] 
    -- Guess you have common ID column in both table for joining
)

Power BI Dax Create New Table From Existing Columns

1 Answers