Incremental Load from Azure Data Lake

[varunaggarwal30]

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

[ImkeF]

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/

[TomMartens]

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

[varunaggarwal30]

Hi @TomMartens@ImkeF

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

[ImkeF]

You must have made a mistake in defining the data-connection string for your data lake.

I'd suggest that you create a new query where you simple connect to your data lake and compare that with the function code you've created. If you cannot spot the difference, you can copy BOTH codes here & I will have a look.

[varunaggarwal30]

@ImkeF

Thanks for your promt reply. I am comparing the two queries. I found some issues. Its working now .The question is how this method will automatically refresh data from power bi service. I believe some one has to manually select the files to send to parameters. Currently all data source for multiple pbix file under one folder in data lake. Do we need to have seperate folders to refresh the data automatically once the file ingest on month basis. 

Thanks,

Varun 

[ImkeF]

If the files are not large, you can import them all every month.

The method you've chosen will not support any incremental load.

[varunaggarwal30]

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