Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Regards,
Lydia
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.
Regards,
Lydia
@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
Regards,
Lydia
@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
@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.
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |