Forum Discussion
Incremental Load from Azure Data Lake
Hi ,
I am new to PowerBI. I have a question. My organization has a requirement to load data in azure data lake and connect power bi with data lake. The input files I get needs to be clenaed and transformed.I am having hard time to understand the data flow. If all the cleansisng happens in powerBI then how will I be able to do incremental load? Files will be ingested every month , how do I create a structured data . Is there any middleware I need to use for ETL process or any other way of doing it better?
Thanks
7 Replies
Hi ,
I am new to PowerBI. I have a question. My organization has a requirement to load data in azure data lake and connect power bi with data lake. The input files I get needs to be clenaed and transformed? I am having hard time to understand the data flow. If all the cleansisng happens in powerBI then how will I be able to do incremental load? Every month file structure will be same if they et appended in data lake , it will be difficult to do clean up. Is there any middleware I need to use for ETL process or any other way of doing it better?
Thanks
You cannot do incremental load in PowerBI desktop currently, but it will come in the premium version.
If the transformation is heavy it could make sense to use an R script to export your results to a database or file and re-import them.
There's also a workaround in PowerBI, but watch out for the traps there: http://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
In addition to the remark from Imke, you may should consider this:
Depending on the amount of data residing in ADL that you want to analyze with Power BI you should consider that the sheer amount will not fit into the data model if you import data.
Accessing data from data sources using DirectQuery may limit the "data munging" capabilities or prevent query folding.
For this reasons you should consider to address the data cleansing outside from Power BI, and instead create a data pipeline using Azure Data Factory maybe in combination with some U-SQL scripts. The goal of this datapipeline is to create a "file" that you can access from Power BI without any further need for M stuff.
Regards
Dataset is not big. It can be easily imported to PowerBI. Only problem is how do I create a structured data table in PowerBI. If transformation is done on one file and upload in data model then next time refresh overide the exisiting data. I tried "http://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/" but when giving data lake as source it gives an error of "An error occurred in the ‘’ query. DataFormat.Error: The supplied file path must be a valid absolute path.".
Looking for urget help
