Log In
Register
Home
Power Query
Xtreme Pivot Tables
July 20, 2016
Offline
Good Day
Please can you assist.
We have data which is downloaded from a company server and on a regular basis and we need to extract the rows of data which have the most recent dates – I don’t need the preceding dates. This is the main source of data but there are three other smaller reports that are linked to the main data – these I will merge with the main data.
I am using MS Office 365 and I have some knowledge of Power Query but not an expert. I have tried using grouping but without success – I think that this is the route to go.
Attached is the sample data for your reference.
- I have removed customer information.
- “Date job closed” is where I need the most recent date for a “vehicle license plate No.”
- The “job number” is for a service or repair on a particular date.
I would appreciate your support to come up with a solution. I am open to other solutions that can be done in Excel.
Thanks very much for your help.
Trusted Members
October 18, 2018
Offline
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"License plate no"}, {{"Latest Closed Date", each List.Max([Date job closed]), type nullable datetime}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Latest Closed Date", type date}})
in
#"Changed Type"
If you then need all the data from the particular rows, then self join the original table to this query. Join on the License plate no and the Date job closed to get only that data for those license plates with the latest date.
Power Query
Xtreme Pivot Tables
July 20, 2016
Offline
Hi Alan
Thanks for your quick response.
Would you be able to select the whole row for each of the latest dates? I will use some of the other information in the row to build a report.
If it is ok with you would you mind explain how you did it?
I appreciate your help
Trusted Members
October 18, 2018
Offline
1. With Power Query or Get and Transform Data depending on what version of excel you have, load your data to the PQ editor.
2. Group your query on the License Tag and select Max Date for the Closed Date.
3. in PQ, copy your original query and remove all steps except the Source.
4. Join the two queries on the common fields of the two queries.
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
File is to large to attach. Here is the second Mcode.
let
Source = Table.NestedJoin(Table1, {"License plate no", "Latest Closed Date"}, Table2, {"License plate no", "Date job closed"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Job number", "Order type", "Plant", "Service advisor", "Service advisor name", "Service advisor surname", "Date job opened", "Order reason", "Order reason description", "Customer account number", "Customer name", "Column2", "Column3", "Job closed status", "Distribution Ch", "VIN", "LV Main type", "Sales office", "Milage", "Column1"}, {"Table2.Job number", "Table2.Order type", "Table2.Plant", "Table2.Service advisor", "Table2.Service advisor name", "Table2.Service advisor surname", "Table2.Date job opened", "Table2.Order reason", "Table2.Order reason description", "Table2.Customer account number", "Table2.Customer name", "Table2.Column2", "Table2.Column3", "Table2.Job closed status", "Table2.Distribution Ch", "Table2.VIN", "Table2.LV Main type", "Table2.Sales office", "Table2.Milage", "Table2.Column1"})
in
#"Expanded Table2"
1 Guest(s)
