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
As variant you may ask them based on dataset to create report with visual which have raw information you need. Having access to such report you may export underlying data into csv or Excel file. Report could be in workspace or in Power BI app.
If to create paginated report it could be exported automatically with Power Automate.
But all of that requires additional work from your IT.
If connect to Power BI dataset cube perhaps that's better to convert PivotTable into cube formulae (or to generate them from scratch for that connection) and adjust them for the corrections. At least that will be refreshable.
If Power Query, formulae or PivotTable, when from another file to keep refreshing.
Hi SergeiBaklan
A bit off topic so only if you know/have time to check...
When you connect a PowerBI Dataset to Excel for further analysis, are the Tables and Measures "loaded" to the workbook Data Model?
If question not clear let me know. Thanks
Forgot to add, we may create implicit measure dragging table field into values if it is allowed for aggregation.
Hi Lorenzo
Connecting to Power BI dataset you are connecting to Analysis Services cube. Thus you see all not-hided measures and tables for this cube. It is not loaded to Excel data model, we have only connection. Thus the only we can do is to create PivotTable based on such cube, or use cube formulae to generate the report.
The only modification we could add that is MDX calculated measure or member. Having into account quite poor intellisense working with MDX in Excel I'd don't recommend this way if only you are not a MDX pro.
In brief, nothing is loaded into Excel data model, we are working with the connected cube.
Crystal clear SergeiBaklan THANKS MUCH!!!
Lorenzo glad to help - if it helps