Forum Discussion
How do I flatten columns into rows in Copy Data activity?
I have a data pull that creates a new column for each row of data as follows:
This is creating a column for each row, but what I need is for it to create a row for each numbered array. Essentially, I need to remove the data.results.#. and have a row for each numbered array.
I tried changing the destination on the right from "data.results.0.amount" to "amount", "data.results.1.amount" to "amount", etc. but then it throws an error saying that there are duplicate columns, so that's not the way to go. The closest thing I could find online was something about a "Flatten activity" and "unroll" which I don't see available in Microsoft Fabric.
Any ideas on how to do this?
I am curious why the data source is creating a data structure like this.
Is it a JSON data source?
It seems like instead of creating each record as a new json object (aka record), it creates a new duplicate of properties.
Is there a limit of how many columns this data source will create? Will it continue to increase the number of columns as time goes?
I think I would try to transpose the columns into rows, you would then get 2 columns with many rows. The first column would contain the original column names, and the second column would contain the values.
(Before you do the transpose, you would need to use the "Use headers as first row" option).
Then I would try to split the content of the first column into three columns, so you will get three columns with this content:
-"data.results."
- the number
- the attribute name
And you will also still have the column which contains the value.
Then I would remove the column which only contains the string "data.results" in each cell.
I would do a pivot on the column which contains the attribute names.
I think that could work.
Something like this:
(this code contains some dummy data I entered, you can paste this entire code in Advanced editor and see what I mean).
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTI0MACSxkBsDsSmII6hUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data.results.0.account_id = _t, data.results.0.amount = _t, data.results.0.invoice_id = _t, data.results.1.account_id = _t, data.results.1.amount = _t, data.results.1.invoice_id = _t]), #"Changed column type" = Table.TransformColumnTypes(Source, {{"data.results.0.account_id", Int64.Type}, {"data.results.0.amount", Int64.Type}, {"data.results.0.invoice_id", Int64.Type}, {"data.results.1.account_id", Int64.Type}, {"data.results.1.amount", Int64.Type}, {"data.results.1.invoice_id", Int64.Type}}), #"Demoted headers" = Table.DemoteHeaders(#"Changed column type"), #"Transposed table" = Table.Transpose(#"Demoted headers"), #"Split column by delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Transposed table", {{"Column1", type text}}), "Column1", Splitter.SplitTextByDelimiter("."), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}), #"Changed column type 1" = Table.TransformColumnTypes(#"Split column by delimiter", {{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", Int64.Type}, {"Column1.4", type text}}), #"Removed columns" = Table.RemoveColumns(#"Changed column type 1", {"Column1.1", "Column1.2"}), #"Renamed columns" = Table.RenameColumns(#"Removed columns", {{"Column1.3", "RowID"}}), #"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Renamed columns", {{"Column1.4", type text}}), List.Distinct(Table.TransformColumnTypes(#"Renamed columns", {{"Column1.4", type text}})[Column1.4]), "Column1.4", "Column2"), #"Changed column type 2" = Table.TransformColumnTypes(#"Pivoted column", {{"RowID", Int64.Type}, {"account_id", Int64.Type}, {"amount", Int64.Type}, {"invoice_id", Int64.Type}}) in #"Changed column type 2"
11 Replies
Hi DevinL ,
I don't think you should have duplicate naming in the first place, then you can follow the steps below.
1. If you are using Azure Data Factory or Synapse Analytics, you can use Data Flow to perform the spreading. The "Spread" transformation allows you to expand an array into separate rows.
2. Select the array to expand. This creates a new row for each element in the array. Or, if your array is nested within another array or object, you can choose to expand the root directory.
3. After unfolding, map the fields from the original structure to the new unfolded structure.
Of course, I searched for an official documentation you can read: Flatten transformation in mapping data flow - Azure Data Factory & Azure Synapse | Microsoft Learn
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply, but this solution doesn't work in synapse Data Engineering.
- I have a data pull that creates a new column for each row of data as follows:
This is creating a column for each row, but what I need is for it to create a row for each numbered array. Essentially, I need to remove the data.results.#. and have a row for each numbered array.
I tried changing the destination on the right from "data.results.0.amount" to "amount", "data.results.1.amount" to "amount", etc. but then it throws an error saying that there are duplicate columns, so that's not the way to go. The closest thing I could find online was something about a "Flatten activity" and "unroll" which I don't see available in Microsoft Fabric.
Any ideas on how to do this? Have you tried using Dataflow Gen2 instead?
(Or notebook)
I don't have much experience with Azure Data Factory, but what I read some time back is that Dataflows Gen2 in Fabric is supposed to cover the role of Mapping Dataflows.
Yes, I've tried using dataflow gen2 but I can't seem to find a way to get these number array columns into rows. In Dataflow gen2 I can import the JSON file and it will expand all of the individual arrays. I don't see a way to put the data into rows instead of one row with many of columns.
I tried to repost this into the Synapse forum, because I didn't mean to post in the Data Factory forum, but then a very "helpful" person merged the topics and stuck it back into the Data Factry forum. XDPerhaps, under the Transform pane, you could utilize the Transpose or Pivot/Unpivot functions.
I found a workaround for this, which doesn't involve Dataflow Gen2. You can unlock a hidden Mapping interface. I've described in detail the approach here : Fabric : Hidden Collection Reference in Copy Activity - Mattias De Smet
Hope it helps!
