Forum Discussion
HELP! How do I automate data cleaning from a continually updating source
(SergeiBaklan Thanks for joining)
That is why I decided to manually download the csv file instead of using the IT teams Power BI dataset, as the source data is in a tabular table form, however I will have to pull the data weekly and drop it into an excel sheet and then use Power Query to automate the cleaning process
NO. Once you have a CSV you don't have to "drop it into an Excel sheet", that step is unecessary
From Excel, you go to Data (tab) > From Text/CSV
you then select your CSV (the Power Query Editor opens) and you begin your cleaning process
Once you have completed the cleaning process and output/returned the query result to an Excel sheet, next time you download a CSV you copy it (overwrite the previous CSV) in the same folder and Refresh your query
I am pretty new to Excel so not 100% versed in everything you have mentioned, but what I can say is that our in houses data team have used the same raw data sent to them automatically from the third party to create a Power BI dataset. They have told me to access this using a Pivot Table in excel, however I am unsure how to pull the underlying data from this, and accessing the Power BI dataset (OLAP) via a Pivot table is very limiting to my ability to automate the data cleaning as I can't turn the data into tabular form (Pivot table parent group restrictions) etc.
That is why I decided to manually download the csv file instead of using the IT teams Power BI dataset, as the source data is in a tabular table form, however I will have to pull the data weekly and drop it into an excel sheet and then use Power Query to automate the cleaning process.
To be honest I am very new to excel so a bit lost 🙂
(SergeiBaklan Thanks for joining)
That is why I decided to manually download the csv file instead of using the IT teams Power BI dataset, as the source data is in a tabular table form, however I will have to pull the data weekly and drop it into an excel sheet and then use Power Query to automate the cleaning process
NO. Once you have a CSV you don't have to "drop it into an Excel sheet", that step is unecessary
From Excel, you go to Data (tab) > From Text/CSV
you then select your CSV (the Power Query Editor opens) and you begin your cleaning process
Once you have completed the cleaning process and output/returned the query result to an Excel sheet, next time you download a CSV you copy it (overwrite the previous CSV) in the same folder and Refresh your query
- Thank you that makes a lot of sense 🙂
