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

[Marck]

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

[Fowmy · Accepted solution]

@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

[Fowmy · Accepted solution]

@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

[Marck]

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 · Accepted solution]

@Marck 

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

[Marck]

Thank you for your help