How do I flatten columns into rows in Copy Data activity?

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"