Forum Discussion
PowerQuery M instead of DAX
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
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
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.
3 Replies
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
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.
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"