Table generated with a table source from azure

[Didierabiven]

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

[v-juanli-msft]

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

[Didierabiven · Accepted solution]

Thanks a lot for your solution. I have a  question, which one should be the best in term of weight  and speed. ?