Combine different columns from many Excel Spreadsheets with data into one in Power BI to model.

That really depends on what the underlying datamodel is. Can we see this as one big table? Because then you can merge the queries. If you don't have a identifier column (ID for example) in all sheets in which the merge can determine what data belongs together, please consider loading all for excel sheets seperately, then add an Index column and merge the queries.

For your convience, I've created two small tables, let's assume these are results of importing two different excel sheets. I want to combine these tables so I have colunns ID, Column1 and Column2 in one 'big' table. 

Table 1Table 2

These examples already have an identifier column (ID) on which I can perform the merge on. If you don't have that, please add an Index column on all tables in Query Editor -> Add Column tab -> Index Column.

Now I am going to merge these two queries. I select query1 (table1) and go to Merge. Choose 'Merge Queries as New'

Select Table2 in the dropdown menu, and select both ID columns in the preview bits. Join kind should be Left Outer.

Now, Press OK and as final step we are going to select which columns you want to merge together in the new table from Table2:

Deselect ID and only leave Column2 selected.

Press OK and look at your new merged table.

Resulting merged Table.

Repeat this step for every subsequent query you need to merge.

That should work. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Hi JarroVGIT 

Thanks a million for your time and effort. This works perfectly fine.

Kind regards, B