SOLVED

Create query from sheet or Pivot Table

Iron Contributor

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.

5 Replies

@Ocasio27 

 

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?

 

 

best response confirmed by Ocasio27 (Iron Contributor)
Solution

@Ocasio27 

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
    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.

@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....

 

@Sergei Baklan 

 

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.

@Ocasio27 

It means you didn't name the range. You could do it directly in name box

image.png

or in Name Manager

image.png

1 best response

Accepted Solutions
best response confirmed by Ocasio27 (Iron Contributor)
Solution

@Ocasio27 

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
    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.

View solution in original post