Normalising nested json data

[chriklev]

Hello!

I have som nested json example data like this:

[
  {
    "Father": "F1",
    "Age": 50,
    "Mother": "M1",
    "MAge": 49,
    "Children": [
      {
        "Name": "C1"
      },
      {
        "Name": "C2"
      }
    ]
  },
  {
    "Father": "F2",
    "Age": 55,
    "Mother": "M2",
    "MAge": 53,
    "Children": [
      {
        "Name": "Cc1"
      },
      {
        "Name": "Cc2"
      }
    ]
  }
]

 
I want to normalise the data in a Dataflow Gen2 so i can put it in one or more tables. This is my Power query:

let
  Source = Lakehouse.Contents(null){[workspaceId = "f1800595-f6c8-4c3d-ac95-11412027b5db"]}[Data]{[lakehouseId = "b14fd83c-651e-4c46-8a29-927271b4d567"]}[Data],
  Navigation = Source{[Id = "Files", ItemKind = "Folder"]}[Data]{[Name = "raw"]}[Content],
  #"Navigation 4" = Navigation{[Name = "example.json "]}[Content],
  #"Imported JSON" = Json.Document(#"Navigation 4"),
  #"Converted to table" = Table.FromList(#"Imported JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
  #"Converted to table"

I expect this query to give me a Table with one column of records and two rows.
This works as expected in the preview while building the Dataflow:

But when i publish my Dataflow to Fabric it gives me this error:

Error Code: 999999, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: Failed to insert a table., InnerException: We cannot convert a value of type Record to type Text., Underlying error: We cannot convert a value of type Record to type Text. Details: Reason = Expression.Error;Message = We cannot convert a value of type Record to type Text.;Detail = [Value = [...], Type = text];Message.Format = We cannot convert a value of type #{0} to type #{1}.;Message.Parameters = {"Record", "Text"};Microsoft.Data.Mashup.Error.Context = User (Request ID: e0134b83-e583-483c-bb21-a8ebf9d61875).

 
Why does the function Table.FromList work differently when publishing to when building the dataflow?
Is there a different way to normalise nested JSON data that works with Fabric?

[lbendlin]

Eventually your Power Query needs to produce a flat table with only basic column types. In Power BI Desktop columns containing records and lists and tables etc are ignored, but in the service they throw errors.

[chriklev]

I see, but even if they dont have a destination?

There is a one to two relation in that im creating two tables from one json source.
So i have three queries in the power query. One query, without a destination, that reads in the json. Then two other queries that reference the first query to further flatten the data into the two seperate tables.

I figured this would be more efficient because the file is only read and parsed once for both destinations.

So each query in the power query needs to be flattened and type cast even if they dont have a destination?

[lbendlin · Accepted solution]

If you enable load the query becomes a partition and must be a flat table of simple column types. You need to disable load for the query "without destination". That will then be an expression rather than a partition.

[chriklev]

A little late, but thank you!