I have the following code, with the following JSON structure: (the elements are lists)
let
Source = Json.Document("[{""var"":[""var1"", ""var2""], ""visitor"":[""vis1"", ""vis2""]}]"),
myTab = Table.FromRecords(Source)
in
myTab
but I would get would be an output like this:
(the elements of the columns would be lists instead of the displayed values):
https://imgur.com/a/HgBA4Tw
However, what I would need, would be an expansion of all the lists in the columns,
as if you would be typing this:
let
Source = Json.Document("[{""var"":""var1"", ""visitor"":""vis1""},{""var"":""var2"", ""visitor"":""vis2""}]"),
myTab = Table.FromRecords(Source)
in
myTab
so my question is there some power query prompt such that
Json.Document("[{""var"":[""var1"", ""var2""], ""visitor"":[""vis1"", ""vis2""]}]")
can be automatically and dynamically be transformed to
Json.Document("[{""var"":""var1"", ""visitor"":""vis1""},{""var"":""var2"", ""visitor"":""vis2""}]")
without changing the JSON beforehand manually? Such that the transformation is done via PowerQuery itself. By dynamic I mean that I wouldn’t have to type any column name, or the number of columns, because I have huge JSON files with always the exact same structure but changing column number/names (there are lists inside the columns which all would have to be converted rowwise)
The number of list elements for all columns are always equal.
What I tried was to manually change the JSON file, but that wouldn’t be feasible for my actual use-case.