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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

26 REPLIES 26
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))

Tomasz59
Frequent Visitor

UPDATE: 

Ok, problem solved. It seems that problem was not in DAX, but in AAS beause changes weren't correctly pushed to data model. After updating Data MOdel correctly, everything works fine. 


Dear  Advocate V, 

 

I used your solution in my data model stored in Analysis Services and it worked for some time, but now returned table is mixed again and I haven't changed anything in source tables. 

Funny thing is that it works fine in  Analysis Services, but table is mixed in PowerBi. 

Do you have any idea what could be  problem?

 

Regards, 

Tomasz

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
Super User

Can you provide screen shot of data sample?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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? 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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...


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.