Forum Discussion
Create query from sheet or Pivot Table
I have a pivot table, which cannot be turned into a Query. I generated a table off it in another sheet but I cannot turn that table into a Query. Only workaround I found was to create a connection to the file and add the sheet itself as a query but this breaks if I move the file and it requires to save the file and then refresh, many times.
Is there any way to add a sheet, not a table, to query connection?
In short, I just need to somehow turn a pivot table into a query, or table, and later use the "fill down" function in Power Query to fill the "null" or empty values.
You may use named range and Power Query it. For example, select entire sheet, name it as mySheet and query it as
let Source = Excel.CurrentWorkbook(){[Name="mySheet"]}[Content] in Sourcehowever much better to narrow the range.
In general I'm not sure that's a good idea to transform data from PivotTable with Power Query, perhaps you may receive the same result playing with data model.
5 Replies
Ocasio27 Hey Friend,
If your query is just to fill the null values in pivot then you can click on Go to special in Home tab and with the help of that you can do it.
But before doing this you will have to copy the data from pivot and paste as values in the another sheet and then use go to special....
You may use named range and Power Query it. For example, select entire sheet, name it as mySheet and query it as
let Source = Excel.CurrentWorkbook(){[Name="mySheet"]}[Content] in Sourcehowever much better to narrow the range.
In general I'm not sure that's a good idea to transform data from PivotTable with Power Query, perhaps you may receive the same result playing with data model.
I get this error. The problem is, there is no table in that sheet
Expression.Error: We couldn't find an Excel table named 'mySheeet'.
Details:
mySheetIt needs to be in a query in order to modify it and export it to Tableau.
It means you didn't name the range. You could do it directly in name box
or in Name Manager
Do you not have the raw data that drives the pivot table?
You should be able to find it if you cursor on the pivot; this will open the pivot tabs at the top right of the screen and you can then 'change data' to locate the source.
or i am missing something?
