Automating daily tasks using Excel/Power Query and SQL

[Stacker]

So I am looking at COVID stats all day and I have to prepare them for my manager. This is a multistage process and it's fairly repetive. As a result i want to automate it so i can spend my days doing more productive **** or cat videos. Either i am not bothered. It's a multi stage process and this is what i have to do.


Step 1

Download a power BI dataset
https://app.powerbi.com/groups/me/apps/....
Save to a certain location
\\sancifs\Policy_and_Governance\Apps\TableauDataSource\PublicHealth\Covid-19\Data\Positive_test_data\Home_visits
Delete first two rows (should be mostly blank) so headings are on row 1
Copy and paste some headings and formulas
Open file from above location
Copy data (not headings) from ‘Cases_to_ddmmyy’ columns AV to BA into the ‘db_import_file’. Paste as values only.
Save and close both files.

Upload to SQL

• Open SQL and go to Databases à PHI_Covid [BW1] à Tables à[BW2] tblPHE_PowerBI_postcode_cases_detailed[BW3]
• Right click > Script Table As > Delete To >New Query Editor Window

Use the below query and then execute:

USE [PHI_Covid]

GO



DELETE FROM [dbo].[tblPHE_PowerBI_postcode_cases_detailed]

GO

Right click on PHI_Covid > Tasks > Import data

Run through import wizard

Select data source as excel
Browse excel file path with previously saved opath
Change destination table to [dbo].[tblPHE.........
Keep clicking next and finish wizard.
THis is the first task and I wanna see if this can be done before i try to do the next.

Thanks

 

[Stacker]

So I think I have found a workaround for linking the datasets to excel. I used this guide and I was able to link excel to this dataset. However when I try to build my table using the pivot table it fails and when I try to move the field settings to the Values section it fails and says "The field you are moving cannot be placed in that area of the report". What can I do? I have column headings like Status, postcode, date entered. How do i make these the column headings whilst keeping their values the values without producing the above error.