Combining Excel Tables

[Anonymous]

Hi all,

I have 3 Excel tables I want to combine into 1. Each excel table has one common column. When I tried to do this by just creating relationships it only allowed me to add sheet A and B to the visualization, when I tried to incluce sheet C everything went blank. I also need to be able to add slicers that let the user filter the data. I will how what I would like the final powerBI table to look like as well as the excel files. Bolded are the common columns

Sheet 1 A

Sheet 1 B

Sheet 1 C

Sheet 1 D

Sheet 1 E

Sheet 2 A

Sheet 2 B

Sheet 2 C

Sheet 3 A

Sheet 3 B

Sheet 3 C

Sheet 3 D

Final Visualization 

Sheet 1 A

Sheet 1 B

Sheet 1 C

Sheet 1 D

Sheet 1 E

Sheet 2 B

Sheet 3 B

Sheet 3 D

[Anonymous · Accepted solution]

Hi @Anonymous ,

Perhaps you can do this by doing the following：

1. After connecting to the spreadsheet, right-click the file name and select Transform Data (don't select each individual sheet):

2.Modify the M code by changing the second argument to "true" (prevents repetition of column headers)

3.In the Data column, click the Expand icon (double arrow) & Uncheck "Use original column name as prefix".

Here's a similar case and a detailed explanation of how to do it.Combining Data From Multiple Worksheets Into one table 

Hope it helps,

Community Support Team _ Caitlyn Yan

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

[parry2k]

@Anonymous Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490