Power Query in Azure Data Factory does not seem to recognize the range in a dataset

Sjef van Zwet 1

I have defined an Excel file as dataset with a specific range. However when using this dataset in Power Query for data wrangling, it is not recognized. BUT if you create an Power Query activity in a pipeline and execute this pipeline, the range is taken into account. This means at design time Power Query gives a different result than at runtime.
I have worked around that by designing after deleting the first rows, but before publishing remove that step. Is it a know bug or am I doing something wrong?

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-01-15T20:13:39.517+00:00

Hello @Sjef van Zwet ,

Welcome to the Microsoft Q&A platform and thank you for reporting this to us.

Before we escalate this issue, could you please provide more details with few screenshots which would help reproduce the issue on our end. Appreciate if you could share a few detailed steps that you have followed.

We look forward to your response. Thank you
Sjef van Zwet 1 Reputation point

2022-01-20T08:21:07.553+00:00

Hi,

as followup and more detail I've added 3 images. The first shows the dataset with range setting. The second shows the Power Query where the autogenerated code does not have any actions to set the range, but it does respect the header setting. The UserQuery is based on me adding a step to skip the first 5 rows in the autogenerated part. This is not saved however. That is why there's an error.
The third picture shows the activity where the Power Query is used. This activity does use the range setting of the dataset and creates the correct output eventhough the Power Query shows an error.

If I skip the first 5 rows in the UserQuery so it can be saved and it has no errors, the activity will go wrong because it will use the range AND the skipping of 5 rows step in the Power Query resulting in skipping 10 rows.

Best regards,
Sjef
Ehren (MSFT) 1,781 Reputation points Microsoft Employee

2022-02-18T20:48:30.783+00:00

Power Query is no longer supported here on Q&A. Please post your question over on https://aka.ms/PQCommunity instead.
Sjef van Zwet 1 Reputation point

2022-02-21T08:10:03.333+00:00

Hi Ehren, this is NOT a Power BI question, but a Data Factory question in relation to Power Query for Data Factory. The answer of @KranthiPakala-MSFT gave me the impression is was going to be escalated once I provided more information. Your answer is not an escalation rather the opposite.

Can you please reevaluate and perhaps escalate the issue (my guess a bug) to the appropriate channel?
Ehren (MSFT) 1,781 Reputation points Microsoft Employee

2022-02-22T22:11:06.7+00:00

If it's a PQ issue, then the PQ Community site is your best bet to get it addressed (even if it's not Power BI-related). If it's an issue with Data Factory and how it integrates Power Query, then it looks like Q&A should be fine.
Sjef van Zwet 1 Reputation point

2022-03-08T15:40:17.037+00:00

Is there any news on this? I did get a message you had entered a reply, but I don't see any new information.
Ehren (MSFT) 1,781 Reputation points Microsoft Employee

2022-03-09T18:41:03.607+00:00

Hi there. As I said above, PQ issues (regardless of the host product) should be posted in the PQ Community site. That said, this particular issue appears to be with ADF's integration of PQ, not PQ itself, and it must have fallen off their radar.

I've directly notified someone from the ADF team.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-03-23T21:04:18.75+00:00

Hello @Sjef van Zwet ,

Thank you for your patience. We have received an updated from the respective product owners that this inconsistency between Power Query authoring and execution is a bug on ADF side and product team is further evaluating it.

To work around this inconsistency, it is recommended not to use the dataset’s range setting instead use “remove rows”/”remove columns” transformation in PQ editor until an out-of-box solution is available.

I will continue to follow-up with product team about this issue and will keep this thread posted as soon as I have further updates from them.

Hope this info helps.