Combining x number of excel sheets to one Power BI Table.

[Anonymous]

Hi - basically I want to merge (I think) x number of excel sheets into one PBI table. The data example is posted below. Bascially the sheets are months, within each sheet the dates of the months are columns. There is a row per sheet for each address I am interested in. The address column with be the link between data on sheets.

So for one sheet I baically want to unpivot all the date columns to get it to rows which is fine.

How do I go about multiple sheets. I guess it could be from 1 to  12 or more.

1) Do I start with import all sheets from excel - can I tell Power Bi to do that?

2) To merge do I need to clean my sheets to get column headers so I can join the data - if so how would I do this x times - not knowing what number of sheets I may have? So for each address I would end up with all the dates from teh sheets as columns - then unpivot.

The alternative is to unpivot all sheets then append to one long list. Either way how would I do this dynamically not knowing how many sheets I may have?

Thanks in advance.

[v-rzhou-msft]

Hi @Anonymous 

I think you can add an index column for each table and then merge them by index column.

My sample:

Remove Index column , remove Top1 row and use first row as column headers.

Then unpivote the columns. Don't forget to select two columns and remove duplicate values.

Result:

You can download the pbix file from this link: Combining x number of excel sheets to one Power BI Table.

Best Regards,

Rico Zhou

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

[Anonymous]

Thanks - so I think I am here adding columns for each table - left joining. Will I eventually run into an error on too many columns. And how would I dynamically go aboit processing x files to join. Thanks very interesting.

[MattAllington]

I blogged about it here https://exceleratorbi.com.au/combine-all-sheets-in-a-workbook-with-power-query/

[Anonymous]

Thanks Matt very helpful, I think with your video I am well on the way to having this solved.