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

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

    • GilesWalker's avatar
      GilesWalker
      Skilled Sharer
      9 years ago

      Anonymous - Thank you very much for your help with this. I have used the new code and it works in the editor however when I click apply and the tables refresh I am getting an error:

       

      OLE DB or ODBC error: [Expression.Error] The 'incrment' argument is out of range..

       

      Do you know what this may be?

       

      Thanks,

       

      Giles

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        GilesWalker,

        I can't reproduce your issue. I would recommend you look into the "APPLIED STEPS" in Query Editor to check which step or which function causes the error.

        And you can share the complete source data so that we can test it.

        Regards,
        Lydia