I have X number of previous months data with a computer name, model, serial number, last online date.
January
A , laptop a , 2ac , 1/17/17
B , laptop a , 5ga , 1/27/17
C , laptop b , n3f , 1/31/17
February
A , laptop a , 2ac , 2/22/17
B , laptop a , 5ga , 2/27/17
D , laptop b , k3a , 2/28/17
March
E , laptop a , 2ac , 2/22/17
F , laptop a , 5ga , 2/27/17
D , laptop b , k3a , 2/28/17
Result:
A , laptop a , 2ac , 1/17/17 , 2/22/17 , null
B , laptop a , 5ga , 1/27/17 , 2/27/17 , null
C , laptop b , n3f , 1/31/17, null , null
D , laptop b , k3a , null , 2/28/17, 3/28/17
E , laptop a , 2ac , null , null , 3/22/17
F , laptop a , 5ga , null , null , 3/27/17
I want to do this operation to the data:
If the computer name exists in the previous month (left table), add a column called "last online date.1" from (right table) "last online date."
If the computer name does not exist in the previous month (left table) add a new row from the new month (right table) to the (left table).
On the next join I want to add "last online date.2" if the computer name exists, and continue to add a new row if it doesn't.
This was the best way I could hack the data together to at least have something to look at but it is unsustainable:
let
Source = Table.NestedJoin(#"Inventory2017-1-3 2-35-15-360",{"Computer Name"},#"Inventory2017-2-14 2-35-11-769",{"Computer Name"},"Inventory2017-2-14 2-35-11-769",JoinKind.FullOuter),
#"Expanded Inventory2017-2-14 2-35-11-1" = Table.ExpandTableColumn(Source, "Inventory2017-2-14 2-35-11-769", {"Last Location", "Computer Name", "Last Checkin", "Status", "Computer Model", "Serial Number", "User ID"}, {"Last Location.1", "Computer Name.1", "Last Checkin.1", "Status.1", "Computer Model.1", "Serial Number.1", "User ID.1"}),
Mergelocation = Table.FromRecords(Table.TransformRows(#"Expanded Inventory2017-2-14 2-35-11-1", (r) => Record.TransformFields(r,{"Last Location", each if r[Last Location] is null then r[Last Location.1] else r[Last Location]}))),
Mergecname = Table.FromRecords(Table.TransformRows(Mergelocation, (r) => Record.TransformFields(r,{"Computer Name", each if r[Computer Name] is null then r[Computer Name.1] else r[Computer Name]}))),
Mergestatus = Table.FromRecords(Table.TransformRows(Mergecname, (r) => Record.TransformFields(r,{"Status", each if r[Status] is null then r[Status.1] else r[Status]}))),
Mergemodel = Table.FromRecords(Table.TransformRows(Mergestatus, (r) => Record.TransformFields(r,{"Computer Model", each if r[Computer Model] is null then r[Computer Model.1] else r[Computer Model]}))),
Mergeserial = Table.FromRecords(Table.TransformRows(Mergemodel, (r) => Record.TransformFields(r,{"Serial Number", each if r[Serial Number] is null then r[Serial Number.1] else r[Serial Number]}))),
Mergeuser = Table.FromRecords(Table.TransformRows(Mergeserial, (r) => Record.TransformFields(r,{"User ID", each if r[User ID] is null then r[User ID.1] else r[User ID]}))),
#"Removed Columns" = Table.RemoveColumns(Mergeuser,{"Last Location.1", "Computer Name.1", "Status.1","Computer Model.1","Serial Number.1", "User ID.1"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Computer Name"},#"Inventory2017-3-14 2-35-10-828",{"Computer Name"},"Inventory2017-3-14 2-35-10-828",JoinKind.FullOuter),
#"Expanded Inventory2017-3-14 2-35-10-828" = Table.ExpandTableColumn(#"Merged Queries", "Inventory2017-3-14 2-35-10-828", {"Last Location", "Computer Name", "Last Checkin", "Status", "Computer Model", "Serial Number", "User ID"}, {"Last Location.1", "Computer Name.1", "Last Checkin.2", "Status.1", "Computer Model.1", "Serial Number.1", "User ID.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Inventory2017-3-14 2-35-10-828",{{"Computer Name", Order.Ascending}}),
Mergelocation1 = Table.FromRecords(Table.TransformRows(#"Sorted Rows", (r) => Record.TransformFields(r,{"Last Location", each if r[Last Location] is null then r[Last Location.1] else r[Last Location]}))),
Mergecname1 = Table.FromRecords(Table.TransformRows(Mergelocation1, (r) => Record.TransformFields(r,{"Computer Name", each if r[Computer Name] is null then r[Computer Name.1] else r[Computer Name]}))),
Mergestatus1 = Table.FromRecords(Table.TransformRows(Mergecname1, (r) => Record.TransformFields(r,{"Status", each if r[Status] is null then r[Status.1] else r[Status]}))),
Mergemodel1 = Table.FromRecords(Table.TransformRows(Mergestatus1, (r) => Record.TransformFields(r,{"Computer Model", each if r[Computer Model] is null then r[Computer Model.1] else r[Computer Model]}))),
Mergeserial1 = Table.FromRecords(Table.TransformRows(Mergemodel1, (r) => Record.TransformFields(r,{"Serial Number", each if r[Serial Number] is null then r[Serial Number.1] else r[Serial Number]}))),
Mergeuser1 = Table.FromRecords(Table.TransformRows(Mergeserial1, (r) => Record.TransformFields(r,{"User ID", each if r[User ID] is null then r[User ID.1] else r[User ID]}))),
#"Removed Columns1" = Table.RemoveColumns(Mergeuser1,{"Last Location.1", "Computer Name.1", "Status.1","Computer Model.1","Serial Number.1", "User ID.1"}),
Thanks for your help,
Jeff
