Append or merge or union two table with different number of columns

[Anonymous]

Table 1

Table 2

I want this

Hi I thought of doing union but union required to have the same exact number of columns. I am still new so I am not sure what I can do. Please help. Thank you in advance.

[SanikaT]

Hi,

Please try this and let me know if it helps,

UnionTable = UNION ( SELECTCOLUMNS ( Table1, "Column1", [Table1 Column1], "Column2", [Table1 Column2], "Column3", [Table1 Column3] ), SELECTCOLUMNS ( Table2, "Column1", [Table2 ColumnA], "Column2", [Table2 ColumnB], "Column3", [Table2 ColumnC] ) )

Thanks,

[Anonymous]

Hi, but I don't have table 1 column 2.

[smpa01]

@Anonymous  try this

Table =
UNION (
    'Table 2',
    SELECTCOLUMNS (
        ADDCOLUMNS ( 'Table 1', "Name", "", "Rate", "" ),
        [Name],
        [No],
        [Rate]
    )
)

[serpiva64]

Hi,

to obtain this

you need to crossjoin your table

Table 2 = CROSSJOIN('Table',SELECTCOLUMNS('Table (2)', "Name", 'Table (2)'[Name]))

then add a key column in each table

Key = 'Table 2'[Name]& 'Table 2'[No]

create a relationship one to many single from your crossjined table

and then it is done.

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[Anonymous]

Hi, It kinda solve my issue but I need the null value because I need to change null value to ok later. So that I can show it on my dashboard.

[serpiva64]

Hi,

You can change it with a measure

Measure = if (HASONEVALUE('Table (2)'[Rate]), min('Table (2)'[Rate]) , "null")

[Anonymous]

Hi, thanks for the help. I created another measure that show blank as ok. May I know is there a way to show it as a pie chart? Currently, I am having a hard time putting the measures into the chart.