cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GilesWalker Established Member
Established Member

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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Power Query assistance

@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.
10 REPLIES 10
Highlighted
Datatouille Established Member
Established Member

Re: Power Query assistance

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 Established Member
Established Member

Re: Power Query assistance

@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 Established Member
Established Member

Re: Power Query assistance

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

Moderator v-yuezhe-msft
Moderator

Re: Power Query assistance

@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.
GilesWalker Established Member
Established Member

Re: Power Query assistance

@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

Moderator v-yuezhe-msft
Moderator

Re: Power Query assistance

@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.
GilesWalker Established Member
Established Member

Re: Power Query assistance

@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

 

Moderator v-yuezhe-msft
Moderator

Re: Power Query assistance

@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.
GilesWalker Established Member
Established Member

Re: Power Query assistance

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 416 members 4,152 guests
Please welcome our newest community members: