Forum Discussion
Table generated with a table source from azure
I try to generate with a SQL table (Table 1) from azure containing more then one millions of line , a new table (Table 2) generated as describe below.
Any Idea what should be the moste efficient way to do it ?
| Table 1 | |||||||
| Contrat | Store | Key | Date debut | Date Fin | Date d'achat | Date de Vente | Nombre de Jours |
| 124505 | 003 | EH1932#1234 | 2017-02-10 | 2017-02-15 | 2017-01-10 | 2017-03-16 | 5 |
| 145012 | 004 | EH19E2#ADES | 2017-01-15 | 2017-01-15 | 2017-05-01 | 2018-04-02 | 0 |
| 145201A | 003 | H1932#13AB | 2017-04-06 | 2017-04-28 | 2017-01-01 | 22 | |
| Table generated | |||||||
| Contrat | Store | Key | Date debut | Date d'achat | Date de Vente | ||
| 124505 | 003 | EH1932#1234 | 2017-02-10 | 2017-01-10 | 2017-03-16 | ||
| 124505 | 003 | EH1932#1234 | 2017-02-11 | 2017-01-10 | 2017-03-16 | ||
| 124505 | 003 | EH1932#1234 | 2017-02-12 | 2017-01-10 | 2017-03-16 | ||
| 124505 | 003 | EH1932#1234 | 2017-02-13 | 2017-01-10 | 2017-03-16 | ||
| 124505 | 003 | EH1932#1234 | 2017-02-14 | 2017-01-10 | 2017-03-16 | ||
| 124505 | 003 | EH1932#1234 | 2017-02-15 | 2017-01-10 | 2017-03-16 | ||
| 145012 | 004 | EH19E2#ADES | 2017-01-15 | 2017-05-01 | 2018-04-02 | ||
| 145201A | 003 | H1932#13AB | 2017-04-06 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-07 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-08 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-09 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-10 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-11 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-12 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-13 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-14 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-15 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-16 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-17 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-18 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-19 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-20 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-21 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-22 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-23 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-24 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-25 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-26 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-27 | 2017-04-28 | 2017-01-01 | ||
| 145201A | 003 | H1932#13AB | 2017-04-28 | 2017-04-28 | 2017-01-01 |
Thanks a lot for your solution. I have a question, which one should be the best in term of weight and speed. ?
2 Replies
Hi Didierabiven
I test with your data using two methods using M in Query Editor and DAX.
With DAX, Create new tables with following formula.
Table = CALENDARAUTO()
Table 2 = CROSSJOIN(table1,FILTER('Table',[Date]<=DATE(2017,4,28)))
Table 3 = FILTER('Table 2',[flag]=1)
Here is pbix using DAX.
In Query Editor, create blank query from New Source->Blank Query, then write M in Advanced Editor.
Here is my pbix using M.
Best Regards
Maggie
Thanks a lot for your solution. I have a question, which one should be the best in term of weight and speed. ?
