Forum Discussion

GilesWalker's avatar
GilesWalker
Skilled Sharer
9 years ago
Solved

Power Query assistance

Hi everyone,

 

I am trying to create a table in Power Query based off of another table in Power Query. The new table needs to have a row of data for every date that occurs between start and finish (inclusive of the these dates). I also require it to pull through all data for that row form the other columns: For example:

 

Current table 

Date from      Date to     Length     Weight

1/1/17            3/1/17      123           15

 

New table:

Date          Length     Weight

1/1/17       123           15

2/1/17       123           15

3/1/17       123           15

 

I know how to do this in DAX however I need it in Power Query so I can then perform a merge to another table later.

 

Hope someone can help with this.

 

Regards,

 

Giles

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    GilesWalker,

    Based on my test, the error is due to the large amount in your table. If I filter the table to keep top 210000 rows, everything works well, if I filter the table to keep top 220000 rows, the error occurs. Consider to reduce the rows in your table.



    Regards,
    Lydia

10 Replies

  • Datatouille's avatar
    Datatouille
    Solution Sage
    9 years ago

    Hi,

     

    Try the following M code:

    let
    Source = YourSource
    GenerateList = Table.AddColumn(Source, "Dates", each List.Dates([Date From],Duration.Days(Duration.From([Date To]-[Date From]))+1,#duration(1,0,0,0))),
    Expand = Table.ExpandListColumn(GenerateList, "Dates")
in
    Expand

     

    Make sure [Date From] and [Date To] are formatted as dates.

    You can even remove these 2 columns after the transformations above.

    • GilesWalker's avatar
      GilesWalker
      Skilled Sharer
      9 years ago

      Datatouille Thanks for the reply. I am getting the an error in the following code:

       

      let
      Source = ServicesDetailed,
      GenerateList = Table.AddColumn(ServicesDetailed, "Dates", each List.Dates([actual_departure],Duration.Days(Duration.From([actual_arrival]-[actual_departure]+1,#duration(1,0,0,0))),
      Expand = Table.ExpandListColumn(GenerateList, "Dates")
      in
      Expand

       

      The error in the advanced editor says Token Comma expected and shows the error at the "in" step. However i cannot seem to figure it out.

       

      The data types of actual_arrival and actual_departure are date/time would this be an issue?

       

      Giles

       

      • GilesWalker's avatar
        GilesWalker
        Skilled Sharer
        9 years ago

        Datatouille - I fixed the issue with the error. However the date/time type was an issue, is there a way to do date time?