Appending Files from a folder

[Sharmile10]

Hi,

I am working on the power bi dashboard, creating the cross-sell - Market analysis. I used the file downloaded from our Google universal analytics product performance with the transaction ID. Everything is working but as all know universal Analytics stopped recording data after the 1st of July. We are currently working on tracking the same thing with GA4. I can see I won't get the same exact columns same as the universal product performance report. 
With universal analytics product performance, every month I download the report by date range and add them to the folder.  Every time I add a new file, the power query automatically updates the data and visuals. 
My question is how I can do with the GA4 product performance report. I want to keep UGA and GA4 records.  Both files will contain different columns. how I can add rules on power query to pull the data from the file?

Sorry if my question is confusing. 

[PhilipTreacy]

Hi @Sharmile10 

Download example PBIX file

What data do you want from both files?

Looking at the GA3 file there's a sheet with 5 column headers in green.  Do you want these?  I've assumed so in my example file.

I stored the GA3 and GA4 files in separate folders.  Then used the From Folder connector to load all files in each folder.  That'll give you 2 queries I've named GA3 and GA4.

The GA4 file has some different column names so it's easy to rename these to match the GA3 file.  Then delete any other columns from both the GA3 and GA4 tables.

You can then Append both queries into a new query, giving this

Please note that to get the queries in my file working, you will need to change the Source location in the Sample queries and in the GA3 and GA4 queries to match the folders/locations of your Excel files.

Does this give you what you were after?

Regards

Phil

[Sharmile10]

@PhilipTreacy  Thank you for providing the file. I will apply this to my data update for you. 

after July 1st I can get the live data to test from GA4. I'll revisit the ticket to mark the solution. 

Regards,

Sharmi

[Sharmile10]

@PhilipTreacy  Thank you, Philip. I'll add the file to Dropbox and share it with you.  First time using the forum.  Please let me know if any problem accessing the file

https://www.dropbox.com/scl/fi/vmj049swbyc4wh9jw1aw2/Product-Performance-20230501-20230531_01_GA3.xl...

https://www.dropbox.com/scl/fi/hpablz3jn9rl3ap2tgxfn/Product-Performance-20230501-20230531_02_GA4.xl...

I have highlighted the column I used on GA3. 

I download the data every month and save it to the folder. 

Thanks in advance.

Sharmi

[PhilipTreacy]

Hi @Sharmile10 

You'll need to save the files from UA and GA4 ni different folders.  Then create a query for each type of file, do the necessary transformations, and merge the two queries.

If you provided some example files with a few lines of dummy data I could show you.

Regards

Phil