question

GeorgeBryant-1809 avatar image
0 Votes"
GeorgeBryant-1809 asked KranthiPakala-MSFT commented

Data flow power query source field recognition issues

Hi

I have a source of an Azure SQL Server which i am trying to utilise a Data Factory power query against. I can build the applied steps out comfortably, it works in preview mode perfectly however i get an error at the top about the field i am referencing. I am using the field and the steps show up and work however i am unable to use it because of this error. It is frustrating because I can clearly see the field and the steps applied to it and clearly working.

80715-capture.png

Am i missing something? Ive checked about every setting and retested multiple times with no answer


azure-data-factoryazure-sql-databasepower-query-not-supported
capture.png (21.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GeorgeBryant-1809 avatar image GeorgeBryant-1809 ·

It should be pointed out that this query works in Power BI M, just very specifically not in Data Factory

0 Votes 0 ·

1 Answer

NaliniNara-4393 avatar image
0 Votes"
NaliniNara-4393 answered KranthiPakala-MSFT commented

@GeorgeBryant-1809, Did you try editing the schema or doing any transformations in the source? When you expand ADFResource, all the sources you have selected in the power query settings will show up here. These datasets are read-only and are not expected to be modified. Any transformations you intend to do should be performed on the user query.

Let us know if doing all transformations on user query solves the issue.

· 5
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GeorgeBryant-1809 avatar image GeorgeBryant-1809 ·

Hi
Yes I am doing all my logic in the user query. It is when creating in user query specifically that it has this error.

0 Votes 0 ·
NaliniNara-4393 avatar image NaliniNara-4393 GeorgeBryant-1809 ·

Hi,
Is it possible that you have changed the PromoteHeaders setting on the dataset after doing some transformations in the powerquery? The setting I am referring to is at the source dataset level and is called "First row as header". This needs to be checked before using it inside power query transformations. If this does not help resolve the issue, please file a support ticket and one of us will reach out to you.

0 Votes 0 ·
GeorgeBryant-1809 avatar image GeorgeBryant-1809 NaliniNara-4393 ·

I did check that yes however because it is coming from an Azure SQL server source this would not apply here.

I will have to submit a support ticket for this as the steps applied are not the issue, it is simply an issue where Power Query is seeing the field in order to have steps used against it but still seems to not recognize that same field. This only arises in Power Query in Data Factory and not Power Query in Power BI.

0 Votes 0 ·
Show more comments