cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Flori_Abb
Helper I
Helper I

Union Formula mixes columns

Hello together

 

to improve the download speed from our datawarehouse, i want to split up the data in a historic table (Data from 2013 up to 27th of December) and a current data load table (data from 28th of December onwards). The sql code is the same, only the date changed. Now i wanted to combine these two tables with the union function, but unfortunatly it writes some buying group names from the buying group column in my sales revenue column and converts this column to text instead of number. 

Has someone a quick idea how i can avoid this?

 

Thanks a lot and KR

 

Florian

 

 

1 ACCEPTED SOLUTION

just a guess, in your dax union formula, maybe try no to reference the table directly, bu use something like

 

union (summarize(table_current, field1,field2),summarize(table_history,field1,field2))

View solution in original post

25 REPLIES 25
Raaassotto
Helper I
Helper I

I have the same issue with mixing columns. It looks like a bug. Union works improperly. My story is absolutely the same as of the author of the topic. Name of the column is the same, order - as well. Data sourse is the same too. Queries are duplicated. The only thing that is changed is period. 

mim
Advocate V
Advocate V

first using the union in the data model instead of append PowerQuery is a very clever idea !!  i was think about it when i read about table partition in SSAS

 

for example 

 

Powerquery 1 load to table 1 - year 2016 - does not change - load only once 

 

PowerQuery 2 load table 2 - year 2017 - do change 

 

then create a calculated table union (table1,table2)

 

as far as i can see the only reason it is not working, because maybe of a bug 

 

for example see this 

 

 

Hello together

thanks a lot for you help and the replies. Unfortunately the problem is not solved, it seems not possible to accelerate the data download by having to identical tables (one with historic) and one with current data. 

The DAX Union Formula mixes up some columns - guess that`s a bug and i have no clue how to avoid that. The append query solution is also not working as it always loads both table, even with unchecking "enable load" and "include in report refresh". The only result what i get with unchecking "enabling load" is a mess as it delets all relationships and measures.

So i accept that all as a bug and hope for a solution in the near future. 

 

BR

Florian

just a guess, in your dax union formula, maybe try no to reference the table directly, bu use something like

 

union (summarize(table_current, field1,field2),summarize(table_history,field1,field2))

View solution in original post

ImkeF
Super User II
Super User II

Very good point mim!

I've included your very valuable tip into my blogpost, describing this solution a bit in detail 🙂

You'll also find a downloadable file there to test this approach: http://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi

 

@ImkeF  and @mim 

Thanks for your hint with Union + Summarize

 

But now i have the impression that the size of my PBIX files is doubling. Can it be?

I am doing so:

Table a 5 Mb

Table b 6 Mb

Table resulting of Union + Summarize = 11 Mb

 

#1 Can that be?

 

I am quite confused here. I thought that what happens with DAX stays in the memory only.  #2 Is that the case or not??

 

Best regards. G.

Yes, that's to be expected.

Filesize doubles, as data doubles. The data will be stored in the file (on the disk, when closed) ane just loaded to RAM once opened and you work with it.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks @ImkeF for your extremely fast & clear reply 🙂

Sorry, i come back to it, not sure to fully understand.

 

Hence, doing Union + Summarize in DAX consumes more place on the hard drive than the Append funtion in Power Query. 😞

 

To be sure i understand the logic,

If table "old" has the old data, the one that should still appear on the report but never change, I should set it to

- Enable load: option selected

- Include in report refresh: option not selected

 

For the table "new", where it should be refreshed and loaded, both options are selected.

 

I am right?

BR, G.

unbelievable, it seems that works. Thanks a lot mim! - i will see it finally tomorrow with the new data refresh.

tested and it works - thanks again!

Hi Florian,

I'm awfully sorry, but you're right: The bug is still there.

I tested it & must have done sth wrong (or it doesn't work consistently, which I doubt).

Don't include in Report Refresh will be ignored once you reference that table from other queries in the query editor or (what I didn't test before) from a DAX-table-creation-operation.

 

Pls leave your support for the bug-solution on the bug-report here - this should really be solved: http://community.powerbi.com/t5/Desktop/Disabling-quot-Include-in-Report-Refresh-quot-works/m-p/1019...

 

Thx!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

parry2k
Super User III
Super User III

Can you provide screen shot of data sample?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi Parry

 

sure, but i guess that`s not really helpful. The column structure is the same in both tables. Also it retrieves the correct data from the Server, only after the union formula it writes at the end the KABEG to the Sales_Rev_Quantity.

 

Columns.JPG

KR

 

Florian

 

Let me ask it again. You have two queries in PowerBI from same sql server but filtered on different date and then you are using append query in PowerBI Model, is this correct understanding? 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I have two queries

one retrieves the historic data Data_AVDWhistoric and

the other retrieves the current data Data_AVDWcurrent

The queries are excactly the same, only the sql retrieval date is different. 

Now i used the formula Data_AVDW = union (data_avdwhistoric;data_avdwcurrent) to get a combined table. That should help me to avoid the long loading time of the historic Datatable.

I thought that would be an easy excercise...

Ah, so you are using DAX UNION formula to combine the two tables. I never used this function but used Append Queries in data model

 

Did you tried that?

 

- Click Edit Queries

- Append Queries

 

Append1.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





here is similar question in the past on this forum

 

https://community.powerbi.com/t5/Desktop/union-formula-column-section-error/td-p/80004






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I have seen that and tried but then i got another error message after a long loading time. Also i had the feeling that with append queries it loads both tables from the datawarehouse. I will try again later and see if it works...


Sure, interested to see how this get resolved? Will be good for other users as well.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors