Last active
October 27, 2023 10:08
-
-
Save ImkeF/fb25fcb541981c29bd72dcccaaec8972 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment