Pivot table in power query

[Anonymous]

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

https://1drv.ms/x/s!AmP8SNf-sgJ7n05LkqLxDYSprZTj?e=bMvHMq   

[Manoj_Nair]

@Anonymous- Check this PBIX file out. Let me know if this works for you.

https://github.com/msncps/Power-BI-Community/blob/main/2023_07_22_Pivot%20table%20in%20power%20query.pbix 

As @rubayatyasmin suggested, you can accomplish this by utilizing the Matrix visuals. In the attached file, you'll find the specific steps that were followed. Moreover, you can reach the same outcome by deactivating the SteppedLayout and adopting the classic style.

[Anonymous]

Hi @Manoj_Nair 
This is not the Result I want.
in the Template below.
Can this result be done with the power query in excel?

[rubayatyasmin]

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]

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 · Accepted solution]

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]

I did what you said but I am getting error.

[rubayatyasmin]

Can you paste the error? It would be easy to troubleshoot. 

Thanks

[Anonymous]

@rubayatyasmin 

[rubayatyasmin]

@Anonymous 

The "Run-time error 9: Subscript out of range" typically occurs when the specified object or element does not exist within the collection. In this case, the error is likely due to the fact that there is no connection named "Combined_Financials_Sector" in the workbook connections.

To resolve this error, you should check if the connection name is correct and make sure that the connection exists in the workbook. To verify the connection name, you can follow these steps:

1. Open your Excel workbook.
2. Click on the "Data" tab in the ribbon.
3. Click on "Connections" to view the workbook connections.
4. Look for the connection named "Combined_Financials_Sector" in the list of connections. If it's not there, you may have used a different name or the connection might have been deleted.

If you find the correct connection name, you can use the following VBA code to refresh the connection in the background with the "Enable background refresh" option checked:

Sub RefreshConnectionInBackground() On Error Resume Next ThisWorkbook.Connections("Combined_Financials_Sector").Refresh On Error GoTo 0 End Sub

This code will try to refresh the connection, and if it doesn't find the connection, it will simply resume the code without raising an error. However, it's essential to make sure that the connection name is correct and exists in the workbook.

If you are still encountering the error after verifying the connection name, please double-check the name spelling and ensure that there are no typos or extra spaces in the connection name. Also, make sure that the connection is created before trying to refresh it programmatically.

[Anonymous]

I checked what you wrote but there is no workbook link.

I checked what you wrote but there is no workbook link.
I am updating the pivot table.

Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        

Sheets("Symbols_Price").Range("Combined_Price_Sector").ListObject.QueryTable.Refresh BackgroundQuery:=True


Worksheets("PT_C").PivotTables("PivotTable1").PivotCache.Refresh



End Sub

It continues to download in the background but I get another error.
Actually, I just need to press "end" for the refresh to finish.

[rubayatyasmin]

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

[Anonymous]

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

I marked the place you mentioned here. but still i am getting error.

[Anonymous]

This may be the poorest table design I've ever seen. 

[Anonymous]

If you have an opinion on the problem, I'd love to hear it.What is the Bad Thing?

Düştüm mapus damlarına öğüt veren bol olur
Toplasam o öğütleri burdan köye yol olur

[rubayatyasmin]

Hi, @Anonymous 

this is what I could achieve in PQ

though not an exact pivot table like Excel. You could use Power BI matrix visual as an alternative to the Excels pivot table. 

[Anonymous]

It's far from the result I want. I am already using the result you showed. thanks