Power Query

[lucreziaparente]

Hello everyone, 

I need your help with Power query on Excel.

I have different datasets in different sheets, as I needed to use them to create a dashboard in a separate tab. to do so, I used Power query to combine the 3 different sheets in 1 only (all data), by using Power query Editor. However now, I am trying to update those 3 sheets with 2023 data, thinking that the all data sheet would have updated itself too.

When I go to the query settings of the All data sheet, I can see my 2023 data, but then they are not reflected in the main table. Do you know how to do this? I hope I was clear :) Thank you!

[#2]

@lucreziaparente 

How do these tables look like before and after updating for 2023 and how exactly did you combine them?

Can you share the file perhaps (OneDrive, Dropbox or similar) or at least some screenshots?

[#3]

@Riny_van_Eekelen 

First of all, thank you so much for your reply!

Unfortunately I cannot share the file, but I will try to explain my self better, step by step.

Goal: create a media dashboard containing all paid media metrics (CTR, VTR, sessions..) for social, display campaigns and so on.
Data sources: Sprinklr, Google Ads, Google analytics, DV360.

Step 1: I downloaded all the reports as CSV files and added all of them in one Excel file.
Step 2: I cleaned the data + I made sure all the sheets (3) have the same number of columns
Step 3: I formatted all the datasets as tables and renamed them
Step 4: I created a new sheet called ALL_DATA using power query

This is how it looks like:

What I need to do now is to update 2023 data for each media source. For example, I downloaded a new report from Google Ads, and after cleaning it I copied paste it to the same Excel sheet with older data. The idea was that by updating all the single sheets, the main ALL DATA one would update automatically too. But I don't know if that's possible.

Thank you!

[#4]

@lucreziaparente I believe it should be possible what you describe, though I'm not convinced that you are doing it correctly. Without seeing the queries it's difficult to judge.

I would probably not copy and paste all CSV data into an Excel file first and then connect to sheets with PQ. I would save the CSV files in a designated folder and then connect PQ to all files in that folder. When new data comes in for 2023, simply put the CSV with 2023 data in that folder and refresh the queries.

But again, I may be mistaken as you haven't shown your data or queries. And the screenshot doesn't reveal much either.

[#5]

True, that's amazing I will try that indeed.
Thanks a lot for the great help!

Best,
Lucrezia