Import multiple MS Office Data Connection files from PowerPivot to ONE PowerBI Model

[AmateurBI]

Hey guys,

i have the following situation that I'm trying to deal with:

Each month I get around 1.3 million rows of data. I'm splitting it into two MS Excel tabs in one worksheet (so e.g. July1 and July2).

In the next step I am combining those two tabs via PowerPiovt (Union All...). When I import one month into my PowerBI datamodel it works just fine. But somehow I can't import more than one MS Office Data Connection into the same PowerBI Datamodel. I can only REPLACE on month by another...

I know my approach is not the most efficient (downloading, spliiting, combining, uploading...) but so far it's the only one that worked haha.

Can you help me out here? Do I have to put every month into one PowerPivot Connection? Or can i import multiple into the same PowerBI Model with a query or something?

Help is much appreciated!

[v-qiuyu-msft]

Hi @AmateurBI,

Not sure I understand you totally. Are there two sheets July1 and July2 exist in the same workbook, and you create a PowerPivot model use those two sheets, then you want to import PowerPivot model to Power BI, right?

As you create a PowerPivot model, you can import a workbook, from Power BI Desktop select File -> Import -> Excel Workbook Contents. We can just import one Excel workbook to one Power BI desktop new file (.pbix). During import, all Power Pivot External Data Connections will be converted to queries in Power BI Desktop. See: Import Excel workbooks into Power BI Desktop.

Besides, Power BI Query Editor provides many data transform features. You can import that two Excel worksheet, then combine them in Power BI desktop. See: Shape and combine data in Power BI Desktop.

Best Regards,
Qiuyun Yu

[AmateurBI]

Hi @v-qiuyu-msft,

yes, that's correct. I have two different sheets July1 and July2 within the same workbook.

So, basically if I wanto to import more months, than I would have to add them to the datamodel of the PowerPivot file?

I mean, if that is not reallly working out, I could still import the sheets one by one and combine them within PowerBI, right?

Thanks for your help! I will check out your links and see if there's anything useful for my case 🙂

[v-qiuyu-msft]

Hi @AmateurBI,

1. So, basically if I wanto to import more months, than I would have to add them to the datamodel of the PowerPivot file?

Yes, you need to add new sheet table into model via Add to Data model option.

2. I mean, if that is not reallly working out, I could still import the sheets one by one and combine them within PowerBI, right?

Without create PowerPivot model in Excel, you can get data from multiple sheets within one workbook in Power BI desktop directly. You can go through articles listed under Transform and shape data here.

Best Regards,

Qiuyun Yu