Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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))
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.
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))
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
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
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
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.
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
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...
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |