Append Data Merge Data

[sbmeder]

When we archive data in our ERP system it moves the transaction data from the current data ARTRAN to an archive table ARYTRN.

My DAX abilities are limited, but I would think I would need to merge? the data in order to use the DAX date and time features to compare periods of time. The data for prior years activity in in ARYTRN and current period is in ARTRAN.  I have attached screenshots of the table structures as they load from my direct query. 

[cgaine]

You can use the DAX method UNION to merge the 2 tables within PowerBI as long as the 2 tables have the same columns. Under the modeling tab select New Table, and enter the expression below.

New Table = union(ARYTRN , ARTRAN)

Hope this helps.

[sbmeder]

Will this method work with direct query?

[sbmeder · Accepted solution]

Thank you for your response. I did an import of the tables and it appended very easily.

Regards,

Scott B. Meder

[v-qiuyu-msft]

Hi @sbmeder,

Based on my test, Append Queries and Append Queries as New are not supported in DirectQuery mode. Assume you connect to SQL Server database, in Get Data wizard, you can write the T-SQL to merge data.

Best Regards,
Qiuyun Yu

[dkay84_PowerBI]

In the query editor you can append without worrying about table structure and the resulting table will have better compression.

[dkay84_PowerBI]

To create one master table, you perform an append.  When you want to blend data from different tables, such as a SQL join or a Lookup (or IndexMatch) from excel, you perform a merge.

[sbmeder]

I seemed to have trouble trying to append the data. Table structures are somewhat different. For some reason, I thought I read that BI would look to see if one table had custno and invdte as data in one it would try and map in the other table?

Regards,

Scott