Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GilesWalker
Skilled Sharer
Skilled Sharer

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

1 ACCEPTED SOLUTION

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

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10

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.

@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

 

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

@GilesWalker,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft 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

@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


1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft - 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

 

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft here is a link to the file:

 

https://www.dropbox.com/s/6im3ezb6ivjqard/Test%20data.pbix?dl=0

 

Thank you for your help with this.

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

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.