Excel 365 Data connection Data Source=$Workbook$

user_6218829 0
Viewed 2169

I got an excel file, created by ex-colleagues.

Excel file has data connection link to some where to pull out the data, how to know the actual path for the source? I only see it linked to Data Source=Workbook;

What is the actual path for the Workbook?

Here is the exported data:

<odc:PowerQueryConnection odc:Type="OLEDB">
    <odc:ConnectionString>Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=TBL_Data (2);Extended Properties=&quot;&quot;</odc:ConnectionString>
    <odc:CommandType>SQL</odc:CommandType>
    <odc:CommandText>SELECT * FROM [TBL_Data (2)]</odc:CommandText>
</odc:PowerQueryConnection>
oledb excel
1 Answers

This connection uses PowerQuery. To get the underlying Server/Database hit the Queries & Connections button on the Data tab of the ribbon and hover your mouse over the connection on the right.

Sometimes PowerQueries use data from other queries so the source won't be apparent unless you right click -> Edit the connection. On the right of the Power Query editor you'll see the ETL steps your data has taken. If you hit the Advanced Editor button on the Home tab you can see this described in Power Query Formula Language.

user_19074340
Related