Loading ODC file/pivot table to Power Query

[AdrianSko]

Hello, I do have an odc file for excel connection, which after opening is giving me access only to create pivot tables or charts based on the query to which I don't have direct access.

Is that possible to load only this pivot table, or directly query output from odc file? I would like to be able to amend the table with other tables, add columns and have the pivot table showing me new data each month, which will be added as a new column in my PBI data model. 

Thanks in advance to any good soul willing to reply to that message 🙂

Adrian

[v-yanjiang-msft]

Hi @AdrianSko ,

Has your problem beed solved? If so, please kindly accept the helpful reply as solution, then we'll close the thread, other people who has the similar problem will benefit from here. Thank you!

Best regards,

Community Support Team_yanjiang

[v-yanjiang-msft]

Hi @AdrianSko ,

According to your description, you have an ODC file that connects to a data source and allows you to create pivot tables or charts in Excel, but you want to load the data into Power BI Desktop and manipulate it further. I have searched the web for some possible solutions, and here are some of the results I found:

• One way to load the data from an ODC file into Power BI Desktop is to use Power Query SharePoint folder connector1. This connector allows you to connect to a SharePoint site and access the file information of all the files and subfolders in a SharePoint folder. You can then filter the files by their extension (.odc) and by their name or path to get the ODC file you want. You can then use Power Query to extract the data from the ODC file and transform it as you wish.
• Another way to load the data from an ODC file into Power BI Desktop is to open the ODC file in Excel and then import the Excel workbook into Power BI Desktop2. This way, you can use the existing pivot table or chart in Excel as a starting point, and then modify it in Power BI Desktop. You can also refresh the data connection in Excel to get the latest data from the source.
• A third way to load the data from an ODC file into Power BI Desktop is to read the server name and database name in the ODC file, and then connect to them directly from Power BI Desktop3. This way, you can bypass the ODC file and access the data source directly. You can then use Power Query or DAX to query and manipulate the data as you wish.

Reference: Share external data by using an ODC file (Power Query) - Microsoft Support

Loading ODC file/pivot table to Power Query - Power Platform Community (microsoft.com)

Solved: How to connect .odc file or Excel file via Power B... - Microsoft Fabric Community

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AdrianSko]

Hi foodd, how can I read the source of the odc file? 

[foodd]

The .odc file is the connection within Excel only. Power BI Desktop can't import or directly use it. However, you can read the source in the .odc file. Then connect it from Power BI Desktop. 

[foodd]

Open the ODC file in Notepad to examine the XML format used.