cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marck
Helper I
Helper I

How to union tables with the same field names but different number of column?

Hi,

 

I am new into PowerBi and I am wondering if it possible to union different tables with same fields but different number of columns.  As some tables have additional fields. I know that it is possible in Tableau. Is this possible in PowerBI? If so someone can help me with this?

 

Thank you

2 ACCEPTED SOLUTIONS
Fowmy
Super User IV
Super User IV

@Marck 

It doesn't allow you to union two tables with different number of columns, however, if you can use SELECTCOLUMNS to select the required column to union the tables like:

New Table= 

UNION(
    SELECTCOLUMNS('Table1',"Col1",'Table'[Column1], "Col2",'Table'[Column1]),
    SELECTCOLUMNS('Table2',"Col1",'Table2'[Column1], "Col2",'Table'[Column2]),
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Fowmy
Super User IV
Super User IV

@Marck 

The code that I shared is to create a table, you can go to Modeling Tab, > New Table  and Past the code below:

 

Fowmy_0-1611580475534.png

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

4 REPLIES 4
Fowmy
Super User IV
Super User IV

@Marck 

It doesn't allow you to union two tables with different number of columns, however, if you can use SELECTCOLUMNS to select the required column to union the tables like:

New Table= 

UNION(
    SELECTCOLUMNS('Table1',"Col1",'Table'[Column1], "Col2",'Table'[Column1]),
    SELECTCOLUMNS('Table2',"Col1",'Table2'[Column1], "Col2",'Table'[Column2]),
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

 

Hi,

 

I have created the following DAX expression: 

UNION(
SELECTCOLUMNS('GA_Cleansed',"Col1",'GA_Cleansed'[Account], "Col2", GA_Cleansed[Campaign_name]),
SELECTCOLUMNS(GA_MICRO_Cleansed,"Col1",'GA_MICRO_Cleansed'[Account],"Col2",'GA_MICRO_Cleansed'[Campaign_name]))

 

However, I get this error message:

'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'

 

How can I fix this?

 

 

Fowmy
Super User IV
Super User IV

@Marck 

The code that I shared is to create a table, you can go to Modeling Tab, > New Table  and Past the code below:

 

Fowmy_0-1611580475534.png

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Thank you for your help

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors