Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    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's avatar
    v-alq-msft
    6 years ago

    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.

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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"