Get data from pivoted excel

[vjnvinod]

hi,

is it possible for Power querty to load pivotted excel and unpivot the excel in power query?

[v-yuezhe-msft]

@vjnvinod,

Based on my test, you can import contents of pivoted Excel  sheets to Power query.


If you need to import data model of Excel pivot, you can use File -> Import -> Excel Workbook Contents option in Power BI Desktop. There is a similar thread for your reference.

https://community.powerbi.com/t5/Desktop/Import-Excel-Pivot-to-PowerBI-Possible/td-p/136729

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft

My question is

Can i import an excel Pivot table into power bi as a table data source?

the one which you have shown is only the content of the pivot, not the actual source data

[v-yuezhe-msft]

@vjnvinod,

As my previous reply, please use File -> Import -> Excel Workbook Contents option in Power BI Desktop.

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft

i did tried that, but it doesn't helps, see the below migration failed

 what i am asking is not about queries or model, its a pivoted xlsm report for which i need the source data to be imported into Power BI

currently, how i am managing is by double clicking on the pivot content and getting into source data and then loading, which is time consuming and for me to do every time the same routine.

[v-yuezhe-msft]

@vjnvinod,

Could you please share the Excel file so that I can repro?

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft

unfortunately the excel file contains confidential information and won't be appropriate to share.

I see the link you have shared, according to that looks like its not possible with a normal excel pivot

[v-yuezhe-msft]

@vjnvinod,

You can share a Excel file containing dummy data here.

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft

Source of that pivot is SQL server, hence the sampling is not possible, because i don't have acess to that server. I have only acess to the Xlsm file

[v-yuezhe-msft]

@vjnvinod,

Could you please describe more details about how the Excel file is created? You can review the steps I use to create the Excel file.

Use this option to connect to SQL in Exceluse the load option to load data to data modelselect "Add this data to the Data Model" option

After the above steps, I create a pivot table as shown in first image  and save the Excel file in xlsm format. And I successfully connect to the Excel in Power BI Desktop.

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft

Hi Lydia,

in my excel, i don't find the option of "show queries" see below.

Can i share you the excel file privately?

[v-yuezhe-msft]

@vjnvinod,

I am using Excel 2016, please check this sample Excel file.  You should find same option in Power Query tab of Excel 2013/Excel 2010.

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft

 while doing that, it ask me for server name, i think this will not work, i have only option of double clicking on the pivot and extracting it to a new excel and save it

[v-yuezhe-msft]

@vjnvinod,

You can follow my previous steps to make a test with your own SQL.

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft

when i followed your steps it asked me for the login credentials

[v-yuezhe-msft]

@vjnvinod,

Yes. You would need to have access to the SQL database you  connect to.

Regards,
Lydia