How to bind different data sets to display a new dataset

[Anonymous]

Hi there,

Pre-Covid we had our staff at their desks and their machines were scanned to them at that location and represented in our asset system, so at any point we could see 'allocated' and 'unallocated' based on the value of th 'username'.  Now we have most people on Thin Clients either in the Studio or the bulk at home gettng rack mounted workstations assigned which means they are not being physically scanned anymore, they get assigned online in the system.

We have built a CSV export from that system which exports the 'hostname' and the 'username' of the rack mounted when they are assigned in the system.

What I would like to do is take the export from our asset system and join it up to the export from the rack mounted system to build up a view of allocated and unallocated.

Asset System Data

HostName	UserName	MachineType	Location
AAA	UnassignedPool	B192	Van
BBB	UnassignedPool	B192	Van
CCC	UnassignedPool	A64	Syd
DDD	UnassignedPool	B96	Syd

RackMounted System

HostName	UserName
AAA	UnassignedPool
BBB	Jack
CCC	UnassignedPool
DDD	Jill

My desired output for the data will be a new column for allocated / unallocated

HostName	UserName	AllocationStatus	MachineType	Location
AAA	UnassignedPool	Unallocated	B192	Van
BBB	Jack	Allocated	B192	Van
CCC	UnassignedPool	Unallocated	A64	Syd
DDD	Jill	Allocated	B96	Syd

This is going to help when we can display totals for our hardware of what is available for use etc and then be able to also see users who have allocations and make sure this has been cleaned up also.

I just can't suss out how to join the two datasets to a new one and then I will have a conditional column update based on the value of the username.

Thanks all.

[bcdobbs]

Hi,

From your description it looks like you need to use the "Append Queries" function in Power Query. 

https://docs.microsoft.com/en-us/power-query/append-queries

Hope that helps.

[Anonymous]

That wont give me the new dataset though.  The Asset System data has a heap of columns I need also, but the rack mount I only need the Username.  If I am appending it I am just adding the rack mount information to the dataset but I don't have the user name inline with the asset system information.

[bcdobbs · Accepted solution]

Sorry I misunderstood. So is HostName common to both systems?

In that case use Merge queries and join on HostName.

[Anonymous]

Thanks, I was just about to reply and ask if I should merge instead 🙂