Jun 16 2020 08:16 AM
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.
Jun 16 2020 08:53 AM
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?
Jun 16 2020 09:38 AM
SolutionYou 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
Source
however 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.
Jun 16 2020 10:58 AM - last edited on Jun 18 2020 01:59 PM by Eric Starker
Jun 16 2020 10:58 AM - last edited on Jun 18 2020 01:59 PM by Eric Starker
@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....
Jun 17 2020 09:04 AM - edited Jun 17 2020 09:10 AM
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:
mySheet
It needs to be in a query in order to modify it and export it to Tableau.
Jun 17 2020 09:19 AM
It means you didn't name the range. You could do it directly in name box
or in Name Manager
Jun 16 2020 09:38 AM
SolutionYou 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
Source
however 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.