cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vjnvinod Member
Member

Get data from pivoted excel

hi,

 

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

15 REPLIES 15
Moderator v-yuezhe-msft
Moderator

Re: Get data from pivoted excel

@vjnvinod,

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

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vjnvinod Member
Member

Re: Get data from pivoted excel

@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

Moderator v-yuezhe-msft
Moderator

Re: Get data from pivoted excel

@vjnvinod,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vjnvinod Member
Member

Re: Get data from pivoted excel

@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.

import excel workbk.JPG

Moderator v-yuezhe-msft
Moderator

Re: Get data from pivoted excel

@vjnvinod,


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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vjnvinod Member
Member

Re: Get data from pivoted excel

@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

Moderator v-yuezhe-msft
Moderator

Re: Get data from pivoted excel

@vjnvinod,

You can share a Excel file containing dummy data here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vjnvinod Member
Member

Re: Get data from pivoted excel

@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

Moderator v-yuezhe-msft
Moderator

Re: Get data from pivoted excel

@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.

1.PNGUse this option to connect to SQL in Excel1-2.PNGuse the load option to load data to data model2.PNGselect "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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 227 members 2,350 guests
Please welcome our newest community members: