Merging multiple tables (more than two) to create a single list of assets

[IanDavies]

I have data from a number of sources about computers in the business, not all sources havr all computers (ergo not all computers are in all sources)

I need to merge the data so that overlapping data is lnked to existing resource and new ones create a new record.

Lets look at example data - my apologies for the horrible tables but the editor kept re-formatting everything I did

Table1

Name	Last Seen	OS	Firewall On
Computer1	4/12/2024	Windows	Yes
Computer2	4/12/2024	Windows	Yes
Computer3	3/5/2020	Linux	no
Computer4	5/12/2024	MacOS	no

Table 2

Name	Last User	MDM
Computer1	Ian	No
Computer2	Mary	Yes
Computer5	Fred	Yes
Computer7	Steven	Yes

Table 3

Name	Last Seen	Software X Installed	Setting Y configured
Computer1	4/12/2024	yes	yes
Computer2	4/12/2024	yes	no
Computer5	4/12/2024	no	no
Computer6	4/12/2024	yes	yes

Ultimately what I need to end up with is somthing like this:

Name	Last Seen	OS	Firewall On	Last User	MDM	Software X Installed	Setting Y configured
Computer1	4/12/2024	Windows	Yes	Ian	No	Yes	Yes
Computer2	4/12/2024	Windows	Yes	Mary	Yes	No	No
Computer3	3/5/2020	Linux	N o
Computer4	5/12/2024	MacOS	No		Yes
Computer5	4/12/2024			Fred		No	No
Computer6	4/12/2024					Yes	Yes
Computer7				Steven	Yes

The final table would be quite big, there are more thna three tables in the original source and I am led to believe that PowerBI does not like big, wide tables, but with no single list of all assets, I think that I need to somehow build that list first and then maybe work to link data accordingly to build out a better star design.

I looked at Table.Combine, but it just appends the tables and I end up with multiple rows (in this example for things lijke Compurter1 and Computer2 that exists in both tables). I dont know how to then merge those records.

I have some limited experience editing M created in steps but if you post code without an explanation on how to implement that on its own I would be lost.

I would love to hear your suggestions on how I can model this data to ensure I have all detail on all assets available without duplicate records. I would be producing PBI dashboards later to show things like how many or in MDM, how many have Software X installed.

Ian

[Anonymous · Accepted solution]

Hi @IanDavies ,

I create three tables as you mentioned.

Then I think you need to make relationships among them.

Next you can go to Power Query and use Append.

You can remove duplicates and use Merge Queries.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IanDavies]

Tahbnk you, I hadnt considerd doing the append followed by the merge ... that is more elegant than my solution.

[Smalfly]

Hi @IanDavies ,

what you are looking for is 'Merge queries':

Clicking it, will open a pop-up window that lets you select the queries (tables) you want to merge.

Let's select table1 and table2 as an example. After having selected the 2 tables using the dropdown box, you need to first choose the column to join the tables on (Name in your example) and then select the join kind 'Full Outer (all rows from both)':

Then you need to repeat that for the remaining tables.

[IanDavies]

Thanks for this, I tried that and I ended up with three different name columns rather than a single column with all the names in it. Like this:

Name

Table2.Name

Table3.Name

Am I to assume at this point, that having merged the three tables as you suggest, the only recourse is to somehow build a single name column to identify the (missing) assets that have been imported from tables 2 and 3?

Ian

[Smalfly]

Yes indeed. Or you create such a column before merging the tables, as suggested by @Anonymous .