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

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

    • GilesWalker's avatar
      GilesWalker
      Skilled Sharer
      9 years ago

      Anonymous thanks for the reply. Unfortonately I require the query to be done at thet minute level after further review of the data. For example:

       

      Date from           Date to               Length     Weight

      1/1/17 12:01       1/1/17 12:03      123           15

       

      New table:

      Date                    Length     Weight

      1/1/17 12:01       123           15

      1/1/17 12:02       123           15

      1/1/17 12:03       123           15

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        GilesWalker,

        You can use the code below.

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQHQnMFQyMrA0MlHSVjBNcYyDU0ApOmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date from" = _t, #"Date to" = _t, Length = _t, Weight = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date from", type datetime}, {"Date to", type datetime}}),
    GenerateList = Table.AddColumn(#"Changed Type", "Dates", each List.DateTimes([Date from],Duration.Minutes(Duration.From([Date to]-[Date from]))+1,#duration(0,0,1,0))),
    Expand = Table.ExpandListColumn(GenerateList, "Dates")
in
    Expand




        Regards,
        Lydia