Search code examples
excelpowerquerym

Power Query M add rows for each nested record from imported json

I have some json data which I'm trying to import in excel using power query but with no luck :

The json looks like this:

{
  "User1@g.com": {
    "2019-03-14T15:23:03": {
      "Message": "A message (1)",
      "Timestamp": "2019-03-14T15:23:03.5042800+01:00"
    },
    "2019-03-15T14:38:34": {
      "Message": "A message (2)",
      "Timestamp": "2019-03-15T14:38:34.3995690+01:00"
    }
  },
  "user2@g.com": {
    "2018-11-02T17:02:19": {
      "Message": "A message (3)",
      "Timestamp": "2018-11-02T17:02:19.8146010+01:00"
    },
    "2019-04-03T09:31:53": {
      "Message": "A message (4)",
      "Timestamp": "2019-04-03T09:31:53.7216810+02:00"
    },
    "2019-04-03T09:43:46": {
      "Message": "A message (5)",
      "Timestamp": "2019-04-03T09:43:46.2697460+02:00"
    }
  },
  "user3@g.com": {
    "2018-11-10T11:03:52": {
      "Message": "A message (6)",
      "Timestamp": "2018-11-10T11:03:52.4458880+01:00"
    },
    "2019-02-27T17:08:02": {
      "Message": "A message (7)",
      "Timestamp": "2019-02-27T17:08:02.4327310+01:00"
    }
  }
}

And I would like the output to look like the following table:

User            message         TimeStamp

User1@g.com     A message (1)   2019-03-15T14:38:34.3995690+01:00
User1@g.com     A message (2)   2019-03-15T14:38:34.3995690+01:00
user2@g.com     A message (3)   2018-11-02T17:02:19.8146010+01:00
user2@g.com     A message (4)   2019-04-03T09:31:53.7216810+02:00
user2@g.com     A message (5)   2019-04-03T09:43:46.2697460+02:00
user3@g.com     A message (6)   2018-11-10T11:03:52.4458880+01:00
user3@g.com     A message (7)   2019-02-27T17:08:02.4327310+01:00

When I import the json, I have the user email column + a column with records

initial

Drilling down in one record shows

enter image description here

And drilling down into this record finally has the message

enter image description here

I already tried a lot of things, and it seems a join would be an option but there a table is needed, not records.

Any help would be greatly appreciated!

Solution

  • Just transform Value column before expanding:

    let
    Source = Json.Document("{
  ""User1@g.com"": {
    ""2019-03-14T15:23:03"": {
      ""Message"": ""A message (1)"",
      ""Timestamp"": ""2019-03-14T15:23:03.5042800+01:00""
    },
    ""2019-03-15T14:38:34"": {
      ""Message"": ""A message (2)"",
      ""Timestamp"": ""2019-03-15T14:38:34.3995690+01:00""
    }
  },
  ""user2@g.com"": {
    ""2018-11-02T17:02:19"": {
      ""Message"": ""A message (3)"",
      ""Timestamp"": ""2018-11-02T17:02:19.8146010+01:00""
    },
    ""2019-04-03T09:31:53"": {
      ""Message"": ""A message (4)"",
      ""Timestamp"": ""2019-04-03T09:31:53.7216810+02:00""
    },
    ""2019-04-03T09:43:46"": {
      ""Message"": ""A message (5)"",
      ""Timestamp"": ""2019-04-03T09:43:46.2697460+02:00""
    }
  },
  ""user3@g.com"": {
    ""2018-11-10T11:03:52"": {
      ""Message"": ""A message (6)"",
      ""Timestamp"": ""2018-11-10T11:03:52.4458880+01:00""
    },
    ""2019-02-27T17:08:02"": {
      ""Message"": ""A message (7)"",
      ""Timestamp"": ""2019-02-27T17:08:02.4327310+01:00""
    }
  }
}
"),
    toTable = Record.ToTable(Source),
    transform = Table.TransformColumns(toTable, {"Value", each Table.FromRecords(Record.FieldValues(_))}),
    expand = Table.ExpandTableColumn(transform, "Value", {"Message", "Timestamp"})
in
    expand

    enter image description here