Table.JsonExpandAll.pq

let
    func = (JSON) =>
        let
            Source = JSON, 
            ParseJSON = try Json.Document(Source) otherwise Source, 
            TransformForTable = 
                if Value.Is(ParseJSON, type record) then
                    Record.ToTable(ParseJSON)
                else
                    #table(
                        {"Name", "Value"}, 
                        List.Zip({List.Repeat({0}, List.Count(ParseJSON)), ParseJSON})
                    ), 
            AddSort = Table.Buffer(Table.AddColumn(TransformForTable, "Sort", each 0)), 
            LG = List.Skip(
                List.Generate(
                    () => [Next = AddSort, Counter = 1, AddIndex = #table({"Sort"}, {{""}})], 
                    each [AddIndex]{0}[Sort] <> "End", 
                    each [
                        AddIndex = Table.AddIndexColumn([Next], "Index", 0, 1), 
                        MergeSort = Table.CombineColumns(
                            Table.TransformColumnTypes(
                                AddIndex, 
                                {{"Sort", type text}, {"Index", type text}}, 
                                "en-GB"
                            ), 
                            {"Sort", "Index"}, 
                            Combiner.CombineTextByDelimiter(".", QuoteStyle.None), 
                            "Sort"
                        ), 
                        PJson = Table.TransformColumns(
                            MergeSort, 
                            {{"Value", each try Json.Document(_) otherwise _}}
                        ), 
                        AddType = Table.AddColumn(
                            PJson, 
                            "Type", 
                            each 
                                if Value.Is([Value], type record) then
                                    "Record"
                                else if Value.Is([Value], type list) then
                                    "List"
                                else if Value.Is([Value], type table) then
                                    "Table"
                                else
                                    "other"
                        ), 
                        AddStatus = Table.AddColumn(
                            AddType, 
                            "Status", 
                            each if [Type] = "other" then "Finished" else "Unfinished"
                        ), 
                        Finished = Table.SelectRows(AddStatus, each ([Status] = "Finished")), 
                        Unfinished = Table.SelectRows(AddStatus, each ([Status] = "Unfinished")), 
                        AddNext = Table.AddColumn(
                            Unfinished, 
                            "Next", 
                            each if [Type] = "Record" then {[Value]} else [Value]
                        ), 
                        RemoveCols = Table.RemoveColumns(AddNext, {"Value", "Type", "Status"}), 
                        ExpandNext = Table.ExpandListColumn(RemoveCols, "Next"), 
                        AddIndex2 = Table.AddIndexColumn(ExpandNext, "Index", 0, 1), 
                        MergeSort2 = Table.CombineColumns(
                            Table.TransformColumnTypes(
                                AddIndex2, 
                                {{"Sort", type text}, {"Index", type text}}, 
                                "en-GB"
                            ), 
                            {"Sort", "Index"}, 
                            Combiner.CombineTextByDelimiter(".", QuoteStyle.None), 
                            "Sort"
                        ), 
                        TransformRecord = Table.TransformColumns(
                            MergeSort2, 
                            {
                                {
                                    "Next", 
                                    each try
                                        Record.ToTable(_)
                                    otherwise
                                        try
                                            
                                                if Value.Is(Text.From(_), type text) then
                                                    #table({"Value"}, {{_}})
                                                else
                                                    _
                                        otherwise
                                            _
                                }
                            }
                        ), 
                        FilterOutNulls = Table.SelectRows(TransformRecord, each [Next] <> null), 
                        Next = 
                            if Table.IsEmpty(FilterOutNulls) then
                                #table({"Sort"}, {{"End"}})
                            else if Value.Is(FilterOutNulls[Next]{0}, type table) = true then
                                Table.ExpandTableColumn(
                                    FilterOutNulls, 
                                    "Next", 
                                    {"Name", "Value"}, 
                                    {"Name." & Text.From([Counter]), "Value"}
                                )
                            else
                                Table.RenameColumns(FilterOutNulls, {{"Next", "Value"}}), 
                        Counter = [Counter] + 1
                    ], 
                    each Table.AddColumn([Finished], "Level", (x) => _[Counter] - 2)
                )
            ), 
            Check = LG{2}, 
            Combine = Table.Combine(LG), 
            Clean = Table.RemoveColumns(Combine, {"Status", "Type"}), 
            Trim = Table.TransformColumns(Clean, {{"Sort", each Text.Trim(_, "."), type text}}), 
            // Dynamic Padding for the sort-column so that it sorts by number in text strings
            SelectSort = Table.SelectColumns(Trim, {"Sort"}), 
            SplitSort = Table.AddColumn(
                SelectSort, 
                "Custom", 
                each List.Transform(try Text.Split([Sort], ".") otherwise {}, Number.From)
            ), 
            ToTable = Table.AddColumn(
                SplitSort, 
                "Splitted", 
                each Table.AddIndexColumn(Table.FromColumns({[Custom]}), "Pos", 1, 1)
            ), 
            ExpandTable = Table.ExpandTableColumn(ToTable, "Splitted", {"Column1", "Pos"}), 
            GroupPos = Table.Group(
                ExpandTable, 
                {"Pos"}, 
                {{"All", each _, type table}, {"Max", each List.Max([Column1]), type text}}
            ), 
            Digits = Table.AddColumn(GroupPos, "Digits", each Text.Length(Text.From([Max]))), 
            FilteredDigits = List.Buffer(Table.SelectRows(Digits, each ([Digits] <> null))[Digits]), 
            SortNew = Table.AddColumn(
                Trim, 
                "SortBy", 
                each Text.Combine(
                    List.Transform(
                        List.Zip({Text.Split([Sort], "."), List.Positions(Text.Split([Sort], "."))}), 
                        each Text.PadStart(_{0}, FilteredDigits{_{1}}, "0")
                    ), 
                    "."
                )
            ), 
            FilterNotNull = Table.SelectRows(SortNew, each ([Value] <> null)), 
            Reorder = Table.ReorderColumns(
                FilterNotNull, 
                {"Value", "Level", "Sort", "SortBy"}
                    & List.Difference(
                        Table.ColumnNames(FilterNotNull), 
                        {"Value", "Level", "Sort", "SortBy"}
                    )
            ), 
            Dots = Table.AddColumn(
                #"Reorder", 
                "Dots", 
                each List.Select(Table.ColumnNames(#"Reorder"), (l) => Text.StartsWith(l, "Name"))
            ), 
            // This sort is just to view in the query editor. When loaded to the data model it will not be kept. Use "Sort by column" in the data model instead.
            Sort = Table.Sort(Dots, {{"SortBy", Order.Ascending}})
        in
            Sort, 
    documentation = [
        Documentation.Name = " Table.JsonExpandAll ", 
        Documentation.Description
            = " Dynamically expands the <Json> Record and returns values in one column and additional columns to navigate. ", 
        Documentation.LongDescription
            = " Dynamically expands the <Json> Record and returns values in one column and additional columns to navigate. Input can be JSON in binary format or the already parsed JSON. ", 
        Documentation.Category = " Table ", 
        Documentation.Source = " . https://wp.me/p6lgsG-Ur . ", 
        Documentation.Version = " 1.2: Added column [Dots] (22/02/2019)", 
        Documentation.Author = " Imke Feldmann: www.TheBIccountant.com . ", 
        Documentation.Examples = {[Description = "  ", Code = "  ", Result = "  "]}
    ]
in
    Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))