DAX Fridays! #36: UNION

Looking for little help,

I using same mathod to append two tables(same headers) in excel power pivot but I cant create a new table(that feature not in power pivot data model). How can I do this using Excel power pivot?

You just save my life!!! haha :-)

Just to confirm, I can apply UNION to 2 or more tables provided that they have the same number of columns?

I have a question, how do you handle two tables with different number of columns like using a union statement Union2 = Union(US, Ind 2)? This was ignored or not treated in this video

I am not able to understand your English, in sql union remove duplicates here combining the data

Why DAX table (created by UNION) is not appearing in the Power Query, while the appended table is appearing in Power Query ? Is there any difference between the space they consume ?

thanks!

Sorry i can't understand this video,
Tell clearly in another video for "UNION"

Hi! Very useful your video but can I ask you something? In a single dataset a have several columns with monthly values, for example (ASSETSJAN, ASSETSFEB, ASSETSMAR....ASSETSDEC). I need to create a new table to merge those columns in a single one and adding an extra column that works as a month indicator. In SQL the function will be something like this: select 1 as Month ASSETSJAN as ASSETS UNION select 2 as Month ASSETSFEB as ASSETS UNION.... until ASSETSDEC. Im trying to think a way to do this in Power BI, can you please help me?

Hi Ruth,

how are you doing... :)

I need a some guidance from you. when I am trying to install power bi desktop in my local system, it is showing some error related to "pipes placed" in local machine. is there a way for clean install of power bi on personal system.

kindly help. will be waiting for your reply.

Thanks and Regards
Kishore

Hi Ruth
Thank you for yet another informative video.

I think if you are using UNION, the safest way (to avoid the complications illustrated in your video) is to have the UNION using SELECTCOLUMNS to select the tables. This way, we can have the column order the same in both the tables. Otherwise, as you have seen, if we change anything to the column formula or name, it puts that column as the right most one in the table and it messes up the table order.

In DAX , Once I combine the two tables with UNION ,

is there anyway to identify which table the record is from.

For suppose, I have multiple tables with the name of 2017 , 2016 , 2015

Once I combine , I need the table name in another column

Please provide the solution in DAX .

Thank You! I will practice in a bit:

interesting approach... I always prefer to use the PowerQuery "Append" function.
PQ does a great job of aligning columns based on their names and preserves the default order of the 1st table... additionally if there are unique columns to the 2nd table not present in the first these are added to the far right.

dangerous function 🤔

Hey Ruth,

It's a bug, when you change position of column it doesn't effect the position which already loaded to datamodel. (Proof: Unload and load again, you will see the result.)
P.s. Union does not remove any dublicates so be aware :)
It works always like: 1st column from first table > 1st column from second table, and continues like this always.
The union here is same as in SQL (union all).