raw_code.txt

let
    /* read the json file, in real life a call to a REST service return json */
    Source = Json.Document(File.Contents("C:\test\pr.json")),
    /* create a empty table */
    emptytable = #table( columns, {} ),
    /* create the list of columns to manage from the json file */
    columns = { "name", "level", "path", "children"},
    /* get the root record and create the root table with it. The MissingFiled.UseNull parameter is important */
    /* because the field <children> won't be there if the record has no children */
    root = Table.FromRecords( { (Source) }, columns,  MissingField.UseNull ),
    /* recursive function that will be call with a list of records */
    get = (childs) => let
      /* here the 'childs' variable is a LIST of records that is never null */
      /* so we create a temp table from that list for the columns we want */
      tbl = Table.FromRecords(childs, columns, MissingField.UseNull),
      /* the goal here is to call the get function for each list of children until the lowest level */
      all = Table.Combine( 
                  /* we need to combine the list of tables created from the get function */
                  List.Combine( 
                    { 
                      /* we combine the tbl from the current recursion */
                      { tbl }, 
                      /* we will call recursively the get function with the list of children for each record of the 'childs' list */
                      /* List.Transform, will call many time the get function for each record of the list */
                      /* so we generate a tmp table and we will combine all of them into one table : all */
                      List.Transform( childs, (c) => if c[children]? = null then emptytable else @get(c[children]?)) 
                    }
                  )
            )
    in
      all,
    /* call the get function with the root children */
    alltables = get(Source[children]),
    /* combine the root with all tables returned from the get function */
    roorall = Table.Combine( { root, alltables } ),
    /* add a column "is_leaf" to distinguish levels considered as parent and the lowest levels that contain records with no children */
    addisleaf = Table.AddColumn(roorall, "is_leaf", each if [children] = null then 1 else 0),
    /* remove the column "children" that contains null if there is no children or a list if there are children */
    removechildrencolumn = Table.RemoveColumns(addisleaf, { "children"}),
    #"Changed Type" = Table.TransformColumnTypes(removechildrencolumn,{{"level", Int64.Type}})
in
    #"Changed Type"