Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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

Table 1

stuck_BI_0-1676359029057.png

 

Table 2

stuck_BI_1-1676359067364.png

 

I want this

stuck_BI_2-1676359091785.png

 

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.

 

7 REPLIES 7
SanikaT
Helper I
Helper I

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
Not applicable

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

smpa01
Super User
Super User

@Anonymous  try this

Table =
UNION (
    'Table 2',
    SELECTCOLUMNS (
        ADDCOLUMNS ( 'Table 1', "Name", "", "Rate", "" ),
        [Name],
        [No],
        [Rate]
    )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
serpiva64
Super User
Super User

Hi,

to obtain this

serpiva64_0-1676362694725.png

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
Not applicable

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.

Hi,

You can change it with a measure

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

serpiva64_0-1676365053369.png

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.