PowerQuery M instead of DAX

[Anonymous]

Hello

I have some probleme with memory "The memory used by the query exceeded the configured limit.".

I think the problem is a table of 5 M rows generate in DAX.

Could someone help me to transform this in PowerQuery M :

Tbl_People_eachDay = 
	SELECTCOLUMNS(
		GENERATE(
			Tbl_People,
			DATESINPERIOD(
				'Tbl_Calendar'[DateKey]
				,Tbl_People[Date_Start]				
				// Date_End also exist
				,Tbl_People[Nb_Days]			
				,DAY
			)
		)
		,"tk_people" , Tbl_People[tk_people]
		,"Day_Presence",'Tbl_Calendar'[DateKey]
	)

Tbl_Calendar : one row for each day

Tbl_People : one row for each person with Date_Start / Date_End / Nb_Days (end - start +1)

Tbl_People_eachDay : one row for each day AND each person (when the day is between start and end...)

Your help could be the direct solution but also just the name of the fonctions i have to use

Thank you

[HotChilli · Accepted solution]

Looks like you want a table from

Bob    12 May 2020    14 May 2020

->

Bob 12 May 2020

Bob 13 May 2020

Bob 14 May 2020

If so, in Power Query, add a column like this

{Number.From([DateStart])..Number.From([DateEnd])}

This will make a list on each row.  You can then Expand the list from the column header.  Then change the datatype to Date

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may add a custom column with the following codes.

let diff = Duration.Days([Date_End]-[Date_Start])+1 in 
List.Dates(
   [Date_Start],diff,#duration(1,0,0,0)
)

Then you need to expand 'Custom' to new rows to get the result.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello

Thanks to both of you, your 2 solutions work well.

The code of the first one is simpliest but later i'm not sure i'll remember and understand it !! 😉

let
Source = ...... ,
#"List_v1" = Table.AddColumn(Source, "List1", each {Number.From([Date_Debut_Ligne])..Number.From([Date_Fin_Ligne])}),
#"Liste_Dates développé" = Table.ExpandListColumn(#"List_v1", "List1"),
#"Colonnes renommées" = Table.RenameColumns(#"Liste_Dates développé",{{"List1", "Date_Presence"}}),
#"Type modifié" = Table.TransformColumnTypes(#"Colonnes renommées",{{"Date_Presence", type date}})
in
#"Type modifié"

The second code is more but also more explicit, i keep this one

let
Source = ...... ,

#"List_v2" =
Table.AddColumn(Source, "List2"
, each let diff = Duration.Days([Date_Fin_Ligne]-[Date_Debut_Ligne])+1
in List.Dates( [Date_Debut_Ligne],diff,#duration(1,0,0,0))
),
#"Liste_Dates développé" = Table.ExpandListColumn(#"List_v2", "List2"),
#"Colonnes renommées" = Table.RenameColumns(#"Liste_Dates développé",{{"List2", "Date_Presence"}})
in
#"Colonnes renommées"

[HotChilli · Accepted solution]

Looks like you want a table from

Bob    12 May 2020    14 May 2020

->

Bob 12 May 2020

Bob 13 May 2020

Bob 14 May 2020

If so, in Power Query, add a column like this

{Number.From([DateStart])..Number.From([DateEnd])}

This will make a list on each row.  You can then Expand the list from the column header.  Then change the datatype to Date