Line delimited array of jsons to table in power query

[Anonymous]

Hi,

I am very new to Power query and having hard time trying to comprehend the solution of the below problem.

What I need to basically do is parse a line delimited array of json and convert it into a table with individual columns.

For example:

Let's say the text file looks like below:

{"messages":[{"current":1, "timestamp":"t1.1"},{"current":2, "timestamp":"t1.2"},{"current":3, "timestamp":"t1.3"}], "id":"id1"}↵

{"messages":[{"current":4, "timestamp":"t2.1"},{"current":5, "timestamp":"t2.2"},{"current":6, "timestamp":"t2.3"}], "id":"id2"}↵

{"messages":[{"current":7, "timestamp":"t3.1"},{"current":8, "timestamp":"t3.2"},{"current":9, "timestamp":"t3.3"}], "id":"id3"}

I need to essentially parse this file and have the output as below:

ID	Current	Timestamp
id1	1	t1.1
id1	2	t1.2
id1	3	t1.3
id2	4	t2.1
id2	5	t2.2
id2	6	t3.3
id3	7	t3.1
id3	8	t3.2
id3	9	t3.3

Could someone please help with the solution of this problem?

[Anonymous]

This works like a charm. Thank you so much @mahoneypat 

[mahoneypat · Accepted solution]

I put your text in a .txt file and brought it in.  You have some atypical characters at the end of the first two lines, so I needed a step to get the text before the last } and then had to replace that back.  From there, Json.Document parses the json on each line and then it is simple expansion of the records/lists.

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Test\JsonText.txt"), null, null, 1252)}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Column1", each Text.BeforeDelimiter(_, "}", {0, RelativePosition.FromEnd}) & "}", type text}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Text Before Delimiter", "Custom", each Json.Document([Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"messages", "id"}, {"messages", "id"}),
    #"Expanded messages" = Table.ExpandListColumn(#"Expanded Custom", "messages"),
    #"Expanded messages1" = Table.ExpandRecordColumn(#"Expanded messages", "messages", {"current", "timestamp"}, {"current", "timestamp"})
in
    #"Expanded messages1"

Pat