Forum Discussion
Combining Excel Tables
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 |
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.
2 Replies
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/38490Hi 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.
