Dynamically Select Columns to Partition by in a Window Activity for an Azure Synapse Data Flow

Question

Dynamically Select Columns to Partition by in a Window Activity for an Azure Synapse Data Flow

Joshua 20

I have an Azure Synapse Analytics Data Flow and am using a Window activity to add a column to show the number of rows with duplicated values for a certain column.

I have no problem with any of my other dataflow expression builder expressions, but in the "Over" section of the window activity's settings, when selecting the columns to "partition-by" (how Microsoft says this setting works https://learn.microsoft.com/en-us/azure/data-factory/data-flow-window) I can't dynamically select the column name using the expression:

byName('col1')

I get an error saying this column can't be found. But I've used that exact expression on multiple different dataflow activities and have never had an issue with this expression's ability to select a column dynamically.

Is there something I'm missing. I would just like to, for the window activity in a synapse dataflow, dynamically select the column(s) to partition by.

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-09-06T03:08:59.1933333+00:00

Hi @Joshua ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

I tried to repro your scenario but didn't receive any error. Could you please share the screenshot of the error.
Joshua 20 Reputation points

2024-09-06T14:15:42.3366667+00:00

The following are 4 screenshots from the dataflow that contains the error I'm getting.

Picture 1: a data preview that shows the data going into the window activity.

Picture 2: selecting partitioning col by name

Picture 3: aggregation on partitioning column

Picture 4: the error message I'm getting. I've tried a few different solutions, but I can't find a way around this error in the window activity. I hope this makes my problem more clear.
Joshua 20 Reputation points

2024-09-06T14:22:18.9133333+00:00

I hope this makes my problem more clear.

Picture 1: Incoming data

Picture 2: Expression used to dynamically select partition column

Picture 3: Aggregation expression

Picture 4: Error message

1 answer

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-09-06T03:08:59.1933333+00:00

Hi @Joshua ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

I tried to repro your scenario but didn't receive any error. Could you please share the screenshot of the error.
Joshua 20 Reputation points

2024-09-06T14:15:42.3366667+00:00

The following are 4 screenshots from the dataflow that contains the error I'm getting.

Picture 1: a data preview that shows the data going into the window activity.

Picture 2: selecting partitioning col by name

Picture 3: aggregation on partitioning column

Picture 4: the error message I'm getting. I've tried a few different solutions, but I can't find a way around this error in the window activity. I hope this makes my problem more clear.
Joshua 20 Reputation points

2024-09-06T14:22:18.9133333+00:00

I hope this makes my problem more clear.

Picture 1: Incoming data

Picture 2: Expression used to dynamically select partition column

Picture 3: Aggregation expression

Picture 4: Error message

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi Joshua ,

Thankyou for sharing the screenshots for the above issue you are facing. It seems you have not specified the column name in the sort tab to order by the column. It is must have property . Kindly provide a column name in sort tab and see if it works.

Hope it works. Thankyou

Joshua 20 Reputation points

2024-09-09T14:30:25.1666667+00:00

Putting in a column for the sort setting is not the issue I'm facing. I've tested filling out the sort setting and it doesn't make any difference. The only thing that I am having trouble with is the window activity not functioning when I am dynamically selecting the column in the "over" section. When I have a static column I can select from the dropdown, the window activity works perfectly fine.

The following pictures show the settings for 2 activities and the data preview for window activity. I have created a work around to show that the only problem I am having is the window activity not working when I try and dynamically select the column in the over section

I am using a derived column activity to create a static column name, assigning it the value of the column I want, then selecting that column in the drop-down. (the sort section is not filled out, and the col: "idk" has the correct values, I've tested it)

The only difference between these set of pictures and the pictures I've sent before (for the window activity) is the selection method of the column in the "Over" section. One is manual selection of a static and identified column seen in the inspect tab, and the other is selecting that same column using the byName() function. (below just shows the identified schema and a set of the actual columns I am reading dynamically; I have no issue in any other activity with dynamically selecting columns)

I saw you tried to recreate my error and couldn't, but you didn't show the data preview tab for that activity. That what caused my error, when trying to see if the activity is working properly with the dataflow through the data preview, I got that error.

Share via

Dynamically Select Columns to Partition by in a Window Activity for an Azure Synapse Data Flow

1 answer

Your answer