Forum Discussion
Get data from Excel workbook files
Struggling to understand documentation when reading:
"If your workbook has simple worksheets with ranges of data, to get the most out of your data in Power BI, be sure to format those ranges as tables"
"How your PowerView sheets and PivotTables and charts appear, or not appear, in Power BI depends on where your workbook file is saved and how you choose to get it into Power BI"
"a new dataset is created in Power BI and data and the data model (if any) from the workbook are loaded into the dataset"
"When you choose Import, any supported data in tables and/or a data model are imported into a new dataset in Power BI"
Created an excel workbook on OneDrive
Inserted a POC range with a couple of columns and rows and turned it into an Excel Table
PBI Service (under same OneDrive credentials) --> GetData --> Files --> OneDrive BUSINESS --> (Connect) --> Import
I can see my Table and Fields in resulting PBI dataset, so far so good
Edit same OneDrive Excel workbook
Add a Power Query import data from another Excel in OneDrive --> LoadTo -->Table and PowerPivot Data Model
Now it has both: "simple" Table and Table connected to PPDM form PQ
Refreshing PBI Dataset fails without any detailed message, this doesn't help to understand but...
Delete Dashboard and Dataset and repeat Import operation in PBI Service:
GetData --> Files --> OneDrive BUSINESS --> (Connect) --> Import same Excel workbook
I can see my Data Model connected Table with fields but "simple" Table is lost
In a previous similar test my source workbook had an Excel PivotTable connected to Data Model, PivotTable wasn't imported into PBI Dataset while I was seeing PPDM Table and fields
Does all this makes any sense?
Is not possible to have tables and/or pivot tables built inside excel in same workbook with a Data Model?
Is not possible to use Excel tools to enreach data coming from different sources and, after, using PBI to create visuals from that "enreached" data?
And if not why?
If my users need to work with Excel tools to prepare data before PBI reporting do they need to copy and paste data locally in their (source) Excel workbook?
Thank you in advance
6 Replies
- Hi there
If I understand correctly whilst you can connect to Excel workbooks with tables, you cannot refresh or edit another Excel workbook that is connected to the data.
The best thing is to import the data into PBI as another source.Thank you GilbertQ for your attention
problem is not about refreshing but about results importing onedrive excel workbook as PBI data source.
(GetData --> Files --> OneDrive BUSINESS --> (Connect) --> Import)
If Workbook source has only excel tables (built from a range + CTL+t with standard blue formatting) I get them in service data source.
If Workbook has both "simple" excel table as above and any data inside Power Pivot Data Model then in service data source I get only PPDM data
Could you help me understand if this is by design (I hope not) or if I'm doing something wrong?
(Either case documentation is misleading imho)
Hi croma ,
How did you do this step? Excel Online or PBI Desktop? Could you explain it to me?
Edit same OneDrive Excel workbook
Add a Power Query import data from another Excel in OneDrive --> LoadTo -->Table and PowerPivot Data Model
Now it has both: "simple" Table and Table connected to PPDM form PQ
Best Regards,
Icey
Hi Icey
I edit workbooks with Excel installed locally in my PC device, where I'm logged in with same credentials used in PBI service, hence saving workbook in OneDrive under same organizational account as PBI, after that import data from service.
PBI Desktop not involved here.
In which way does PBI service import procedure looks for data to be published from inside workbook?
I understand it ignores any range data not formatted as a Table, this isn't an issue, but as per my experience when it finds data in data model then it ignores "simple" Excel Tables and Pivot Tables.
Could you please confirm expected behavior? Is there any example or documentation I missed?
Thank you
As Icey mentioned it, I now tested with PBI Desktop (Version: 2.80.5803.1061 64-bit), keeping things siimple tried getting data from an on-premise Excel workbook containing:
- "simple" sheet ranges
- sheet ranges turned into Tables
- Power Query data loaded ONLY to Data Model (Load To --> Only Connection + Data Model)
- Power Query data loaded ONLY to excel table (not to Data Model)
- Pivot Table from Data Model.
Using Get Data option I can get everything which is visible on the "surface" of my sheets as when I open workbook with Excel (thus excluding Power Query data with only connection loaded to Data Model, but including anything else: ranges, tables, Pivot Table from Data Model, Power Query data loaded to tables)
Using Import option --> "Power Query, Power Pivot, Power View" I actually get:
Power Query Data loaded ONLY to Data Model
No ranges, excel tables, Pivot tables
Strangely enough, about Power Query originally loaded ONLY to excel Table:
I get the Query in pbix, I can see and edit it in "Transform data" but can't understand how to include it in "Fields" section of any report, it isn't visible in Dataset diagram either, and "Close & Apply" simply does nothing. What can I do with these queries?
Well...I'm really not able to find any sense in all this "Get data from excel" for both Desktop and Service version, hoping you'll prove me wrong but the impression is that presence of a Data Model in original workbook is like a switch: you can get one kind of data and not the other.
Unfortunately documentation doesn't help much.
