Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Pivot table in power query

hello, there is a help to make the Pivot table in the picture in a power query.   Picture of the table below. It will make the job much easier and simpler in a situation like the first pictur...
Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    ThisWorkbook.Connections("Combined_Financials_Sector").Refresh

     

    note that Background refresh option should be checked. In the Excel UI, you can access this setting by clicking on "Connections" in the "Data" ribbon, selecting a connection, and clicking on "Properties". The "Enable background refresh" setting is in the dialog box that opens.

     

     

     

rubayatyasmin's avatar
rubayatyasmin
Community Champion
3 years ago

To my knowledge, you can not achieve the same result in PQ. 

 

I am curious if it's possible. Let me know here if you are able to accomplish it in PQ. Maybe some advanced M code can do the trick. 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

you're right, it's a difficult process.
If I import it directly from the power query as a pivot table report, I have a problem.

I am currently renewing with these codes.

Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        
Sheets("Combined_Financials").Range("Combined_Financials_Sector").ListObject.QueryTable.Refresh BackgroundQuery:=True
Sheets("Symbols_Price").Range("Combined_Price_Sector").ListObject.QueryTable.Refresh BackgroundQuery:=True
End Sub

 How do I refresh the "Combined_Financials_Sector" in the background.

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    ThisWorkbook.Connections("Combined_Financials_Sector").Refresh

     

    note that Background refresh option should be checked. In the Excel UI, you can access this setting by clicking on "Connections" in the "Data" ribbon, selecting a connection, and clicking on "Properties". The "Enable background refresh" setting is in the dialog box that opens.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I did what you said but I am getting error.

      • rubayatyasmin's avatar
        rubayatyasmin
        Community Champion
        3 years ago

        Sorry, this is not English. I can not read it.