Pivot friendly data

Hi,

I need to analyse revenue/profit data period by period last year vs this year but the format I extract it from the database in is not pivot-friendly.

Is there a way to automate the data transformation so I can paste new data into a document and refresh pivots so I can do the analysis I need?

I need to view each owner and compare a period this year vs last year. I also need to view the numbers as a whole so the total for the team in a period this year vs last year.

Thanks.

Hi, Power Query code..

Please Login or Register  to view this content.

Originally Posted by CK76

Is there always 13 periods in a full year?

Yes, always 13 x 4 week periods every year.

Originally Posted by veyselemre

Hi, Power Query code..

Please Login or Register  to view this content.

Thanks, but I wouldn't have clue what to do with this.

I did as instructed but upon refresh, I get an error message...

"Query 'tableData' 9step 'myData') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination".

I've opened Queries and Connections and double-clicked on 'tableData' and I can see the same error/warning message with a button to edit settings on the first 2 steps. I can see the list of steps on the right-hand side, although all in Turkish.

Attachment 868507

I've clicked the edit settings button and pasted the file path and am now being presented with the below issue from step 9 onwards.

'Expression.Error: We cannot apply operator & to types Text and Table.
Details:
Operator=&
Left=-
Right=[Table]'

I'm stuck at this point and unsure if copying and pasting the file path was the right thing to do.

Attachment 868508

Originally Posted by CK76

Here's steps.

1. Make sure to save the file in local drive.
2. Initially formula will give error. Make sure that you change "dosyaadi" with "filename" and evaluate. Check that it returns valid path.
3. Refresh Data. Table.

There should be no errors generated.

If that doesn't work... Let me see if I can create full tutorial on steps.

Saved on Desktop, evaluated, and returns valid path.

Refresh gives the same error.

Converted the images to jpg and tried again in the hope these work.

Attachment 868550

&

Attachment 868551

Copy of workbook. The only thing I have done is change the filename.

I've managed to figure it out. There is a privacy issue. Not sure how to get around it without building it myself. If you have a tutorial, I'd really appreciate it.

Thanks.

It is best to manually write the address of your file in cell A2 on the filename page. Can you try right-clicking on the green table in the data page and refreshing?

Originally Posted by veyselemre

It is best to manually write the address of your file in cell A2 on the filename page. Can you try right-clicking on the green table in the data page and refreshing?

That worked. Amazing, thank you.

Originally Posted by veyselemre

It is best to manually write the address of your file in cell A2 on the filename page. Can you try right-clicking on the green table in the data page and refreshing?

A quick question... can I rename the tabs without affecting the workbook?

Originally Posted by blackburnsexcel

A quick question... can I rename the tabs without affecting the workbook?

Please Login or Register  to view this content.

"Sheet 1" in the Power Query code shows the sheet name. You can edit here.

I decided to use it as a separate workbook I can reference from my Dashboard in another workbook so no need now.

This helped to create a fantastic dynamic dashboard that presents company data beautifully. And now it's easy to update with new data. I couldn't have done it without you, I really appreciate you taking your time out.

Thank you.

I'm glad I could help. It was a nice brain exercise for me while learning PQ.