Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply

Pivot column in power query editor using data connected to a folder

My data source is from a SharePoint folder. When connecting the data to Power Query Editor, I want to transform it into a pivot table using the function Pivot Column. However, there are some fields/values in the table that I want to remain untouched from the pivot column transformation. What I would do, in that case, is to duplicate the table and then transform said duplicated table into a pivot table. However, will the pivot table update if the data in my SharePoint folder is updated? Or to put it another way, is duplicating the table the right way for me to achieve the effect I want it to achieve? 

1 ACCEPTED SOLUTION

Assuming your question is: 

I know that Table1 will update, as they are connected. But will Table2 update as well, as it's just a duplication?

Yes, the duplicate table will also update if the data in the sharepoint changes. All you need to do is to set up the refreshe schedule from power bi service or if you are working only in desktop then you need to manually refresh your report.


If the post helps please give a thumbs up


If it solves your issue, please accept it as the solution to help the other members find it more quickly.


Tharun

 

View solution in original post

6 REPLIES 6
tharunkumarRTK
Super User
Super User

@smallfires0628 

could you share the sample data and also your expected result?

My data connected from the SharePoint folder is something like this. I'll call it Table1.

Booking IDFinancial AccountAmountCustomerSupplier 
HD12Table sales10AAB
HD12Table sales5BDF
HD12Chair sales10CGH
HD12Chair sales5DTO
HD24Table sales10EPI
HD24Table sales6DOV
HD24Chair sales10FGH
HD24Chair sales6YDI

I want to create a pivot table using Pivot Column in Power Query Editor. For that, I only need the columns Booking ID, Financial Account, and Amount. But I want to keep the columns "Customer" and "Supplier" in Table1, as I will use them for other purposes. 

So, in that case, I'll duplicate Table1, delete columns "Customer" and "Supplier." The new table without columns Customer and Supplier is Table2. I'll use Table2 to create a pivot table.

If I update the data in my SharePoint folder, I know that Table1 will update, as they are connected. But will Table2 update as well, as it's just a duplication?

Assuming your question is: 

I know that Table1 will update, as they are connected. But will Table2 update as well, as it's just a duplication?

Yes, the duplicate table will also update if the data in the sharepoint changes. All you need to do is to set up the refreshe schedule from power bi service or if you are working only in desktop then you need to manually refresh your report.


If the post helps please give a thumbs up


If it solves your issue, please accept it as the solution to help the other members find it more quickly.


Tharun

 

Can you please provide Expected Results too

The pivot table would be something like this: 

pivot table.PNG

So I don't need column "Customer" and "Supplier" but I would like to keep them anyway because I need them for other purposes.

You can do this by single table if that is what you are expecting. Please find the screenshot below

booking.png

 

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.