Dataflow - currently not as capable as PBI Desktop Power Query?

[Alex_Rajkov]

Hi, I am testing dataflow. Trying to migrate my common datamodel from lots of redundant PBI files to the centralized dataflow service. Great and much needed feature!

Knowing its still in preview status, I found some issues..

1. Dataflow, with the underlying azure datalake, should be more capable than a local machine, right? I transferred one of my bigger queries and run into a "stack overflow". The table contains 15 columns and up to 1 million rows (including the joins). So nothing special. The ETL steps contain severeal joins, one of them with 2 matching columns. This is the step where dataflow stops processing. Reducing the rows for testing gets the job done (the query logic stays the same), so it is a performance issue? I wasn't expecting this.

2. I am not shure whether or not Power Query already has the same capabilities as in PBI Desktop.

Can you use centralized parameters and functions? I think I did so for parameters a) centralized and b) as first step in the query. Funcitons i didn't test yet.

Can you perform a join the resulting table from previous query step against itsself? This is the step where I got the issue in (1.).
Spliting the query into 2 and adding a 3rd query for comining / joining didn't change the issue.

Do the same limits while connecting to QAuth 2.0 webinterfaces exist? Explanation: While PBI Desktop can connect to a QAuth 2.0 webinterface where you have an login page and refresh, the service can't.

3. While refreshing an entity within the entity editor works fine, the same query doesn't refresh. Error "operator > cannot be used with date or datetime".

KR
Alex

[v-shex-msft]

Hi @Alex_Rajkov,

#1, Nope, I haven't found a document mentioned performance difference between local machine and azure service.

Query performance are based on device resources, if these functions not have enough resource, it may not better than local machine who has great resources.

As you said, data flow functions still in preview, so I think azure service only share a part of idle resource for use and test on these features(without any optimization on them).

If this is a case, it obvious not has better performance than another services who has standalone resource and particular optimization.

#2. Actually they are different type things, you can take a look at below link to know the difference between them:

Difference between PowerPivot, PowerQuery and PowerBI

#3, Did you test to customize steps in power query? If this is a case, please try to convert them as same type before compare them.(e.g. Date.From, DateTime.From)

Regards,
Xiaoxin Sheng

[Alex_Rajkov]

QHi @v-shex-msft,

thank you for taking the time to look at this.

#1: yep, I guess I just have to be patient...the reasons for reduced capacity in the preview status are reasonable. I just think it is worth to mention the issues right now while the PBI team is developing the feature. It could help.

#2: yes I am well aware of the different tools. I am speaking of Power Query capabilities IN PowerBI Desktop,vs Power Query Capabilities in Power BI Service / to be exact in DataFlows.

Is the later capable of defining / referencing parameters, functions and so on?

#3: The matching keys are of the same data type. I will continue to test and will isolate the step and remove all unneeded columns.

KR Alex

[v-shex-msft]

Hi @Alex_Rajkov,

>>Is the later capable of defining / referencing parameters, functions and so on?

Based on my test, it is possible to create function in data flow but it seem not recognize it as a valid function and you can't use it in other query.

>>The matching keys are of the same data type. I will continue to test and will isolate the step and remove all unneeded columns.

I'm not so clear for this, can you please share some sample data for test?

Regards,

Xiaoxin Sheng

[mllopis]

You should be able to define M functions in PQ Online by editing the M code (i.e. via Advanced Editor). However, the experiences for function authoring are much richer in Power Query Desktop. You can expect over time Power Query Online will get to parity with Power Query Desktop (incrementally). 

Thanks,
M.