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 ...
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

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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        GilesWalker,

        You can convert the date columns from Date/Time type to Date type, then apply the M code that Excelside provides.


        Regards,
        Lydia