Forum Discussion

croma's avatar
croma
Icon for Advocate II rankAdvocate II
6 years ago

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 t...
Need Help
Tutorial Requests
croma's avatar
croma
Icon for Advocate II rankAdvocate II
6 years ago

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

croma's avatar
croma
Icon for Advocate II rankAdvocate II
6 years ago

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.

 

 

 

  • croma's avatar
    croma
    Icon for Advocate II rankAdvocate II
    6 years ago

    Any help on this?

     

    Thanks in advance