Importing nested json as datasource

[BjorntobeWild]

Hi,

I'm having a json-file with the following structure:

{
"DataSource": {
"Id": "b94809c324ee4ab5b7dfa81e00b1e535",
"Name": "Storingen",
"Headers": [
{
"Name": "ID",
"DisplayAt": "None"
},
{
"Name": "Datum storing",
"DisplayAt": "Title"
},
{
"Name": "Tijdstip melding",
"DisplayAt": "None"
},
{
"Name": "Projectnummer",
"DisplayAt": "None"
},
{
"Name": "Projectnaam",
"DisplayAt": "None"
},
{
"Name": "Servicemonteur",
"DisplayAt": "None"
},
{
"Name": "Start reistijd",
"DisplayAt": "None"
},
{
"Name": "starttijd werkzaamheden",
"DisplayAt": "None"
},
{
"Name": "eindtijd werkzaamheden",
"DisplayAt": "None"
},
{
"Name": "eind reistijd",
"DisplayAt": "None"
},
{
"Name": "omschrijving storing",
"DisplayAt": "None"
},
{
"Name": "oorzaak storing",
"DisplayAt": "None"
},
{
"Name": "omschrijving werkzaamheden",
"DisplayAt": "None"
},
{
"Name": "aanbevelingen",
"DisplayAt": "None"
},
{
"Name": "verbruikte materialen",
"DisplayAt": "None"
},
{
"Name": "zoekstring",
"DisplayAt": "None"
},
{
"Name": "categorie_ID",
"DisplayAt": "None"
},
{
"Name": "melding_van",
"DisplayAt": "None"
}
],
"Rows": [
[
"073a7f4a-4ac6-4f3f-8f39-a89400d1116b",
"2018-02-26",
"13:41:00",
"104",
"Projectnaam X",
"Name of mechanic",
"11:00:00",
"11:30:00",
"12:45:00",
"13:15:00",
"Description",
"Onbekend",
"Some text",
"The solution.",
null,
"Search string",
"1",
null
],
[
"4081210e-8abf-42df-8a18-a89400d514e6",
"2018-02-27",
"13:55:00",
"177",
"Projectnaam Y",
"Name of mechanic",
"11:00:00",
"11:30:00",
"12:45:00",
"13:15:00",
"Description",
"Onbekend",
"Some text",
"The solution.",
null,
"Search string",
"6",
null
],
],
"TotalRows": 367,
"LastUpdated": "2022-10-14T19:06:29.4870000Z",
"CompanyId": 12345
}
}

I would like to get all the Rows from the json file into my table. I've been puzzling around for a few hours with converting to table and expanding headers and lists, but I don't get the result I want.

The best result I get is that all the rows are vissable in one column. But I have (and need) 18 columns with all the values in the right column.

Can anybody tell me how to achieve my goal?

[BjorntobeWild · Accepted solution]

I figured it out.

I had to add a custom column with the following code:

#"Aangepaste kolom toegevoegd" = Table.AddColumn(#"DataSource.Rows uitgevouwen", "Aangepast", each Table.Transpose(Table.FromList([DataSource.Rows],Splitter.SplitByNothing(),null,null,ExtraValues.Error)))

let
Bron = Json.Document(Web.Contents("URL")),
#"Geconverteerd naar tabel" = Table.FromRecords({Bron}),
#"DataSource uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel", "DataSource", {"Id", "Name", "Headers", "Rows", "TotalRows", "LastUpdated", "CompanyId"}, {"DataSource.Id", "DataSource.Name", "DataSource.Headers", "DataSource.Rows", "DataSource.TotalRows", "DataSource.LastUpdated", "DataSource.CompanyId"}),
#"DataSource.Rows uitgevouwen" = Table.ExpandListColumn(#"DataSource uitgevouwen", "DataSource.Rows"),
#"Aangepaste kolom toegevoegd" = Table.AddColumn(#"DataSource.Rows uitgevouwen", "Aangepast", each Table.Transpose(Table.FromList([DataSource.Rows],Splitter.SplitByNothing(),null,null,ExtraValues.Error))),
#"Andere kolommen verwijderd" = Table.SelectColumns(#"Aangepaste kolom toegevoegd",{"Aangepast"}),
#"Aangepast uitgevouwen" = Table.ExpandTableColumn(#"Andere kolommen verwijderd", "Aangepast", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18"}, {"Aangepast.Column1", "Aangepast.Column2", "Aangepast.Column3", "Aangepast.Column4", "Aangepast.Column5", "Aangepast.Column6", "Aangepast.Column7", "Aangepast.Column8", "Aangepast.Column9", "Aangepast.Column10", "Aangepast.Column11", "Aangepast.Column12", "Aangepast.Column13", "Aangepast.Column14", "Aangepast.Column15", "Aangepast.Column16", "Aangepast.Column17", "Aangepast.Column18"}),
#"Custom rename" = Table.RenameColumns(#"Aangepast uitgevouwen",List.Zip({Table.ColumnNames(#"Aangepast uitgevouwen"),#"Kolomnamen"[DataSource.Headers.Name]})),
#"Kolommen verwijderd" = Table.RemoveColumns(#"Custom rename",{"ID"}),
#"Type gewijzigd1" = Table.TransformColumnTypes(#"Kolommen verwijderd",{{"Tijdstip melding", type time}, {"Datum storing", type date}, {"Projectnummer", Int64.Type}, {"Projectnaam", type text}, {"Servicemonteur", type text}, {"Start reistijd", type time}, {"starttijd werkzaamheden", type time}, {"eindtijd werkzaamheden", type time}, {"eind reistijd", type time}, {"omschrijving storing", type text}, {"oorzaak storing", type text}, {"omschrijving werkzaamheden", type text}, {"aanbevelingen", type text}, {"verbruikte materialen", type text}, {"zoekstring", type text}, {"categorie_ID", Int64.Type}, {"melding_van", type text}}),
#"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Type gewijzigd1", "Tijdstip storingsmelding", each [Datum storing] & [Tijdstip melding]),
#"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Aangepaste kolom toegevoegd1",{"Tijdstip storingsmelding", "Datum storing", "Tijdstip melding", "Projectnummer", "Projectnaam", "Servicemonteur", "Start reistijd", "starttijd werkzaamheden", "eindtijd werkzaamheden", "eind reistijd", "omschrijving storing", "oorzaak storing", "omschrijving werkzaamheden", "aanbevelingen", "verbruikte materialen", "zoekstring", "categorie_ID", "melding_van"}),
#"Type gewijzigd" = Table.TransformColumnTypes(#"Volgorde van kolommen gewijzigd",{{"Tijdstip storingsmelding", type datetime}}),
#"Aangepaste kolom toegevoegd2" = Table.AddColumn(#"Type gewijzigd", "Dagnaam", each Date.DayOfWeekName([Tijdstip storingsmelding], "nl-NL")),
#"Aangepaste kolom toegevoegd3" = Table.AddColumn(#"Aangepaste kolom toegevoegd2", "Dagnummer", each Date.DayOfWeek([Tijdstip storingsmelding], 1))
in
#"Aangepaste kolom toegevoegd3"

[Anonymous]

Hi @BjorntobeWild ,

Can you share a sample output in table format? This will be very helpful, thanks!

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[BjorntobeWild]

Hi Gao,

The result has to be like the table below.

ID

Datum storing

Tijdstip melding

Projectnummer

Projectnaam

Servicemonteur

Start reistijd

starttijd werkzaamheden

eindtijd werkzaamheden

eind reistijd

omschrijving storing

oorzaak storing

omschrijving werkzaamheden

aanbevelingen

verbruikte materialen

zoekstring

categorie_ID

melding_van

073a7f4a-4ac6-4f3f-8f39-a89400d1116b

2018-02-26

13:41:00

104

Projectnaam X

Name of Mechanic

11:00:00

11:30:00

12:45:00

13:15:00

Description

Onbekend

Some tekst

The solution

Search string

1

4081210e-8abf-42df-8a18-a89400d514e6

2018-02-27

13:55:00

177

Projectnaam Y

Name of Mechanic

10:30:00

11:30:00

15:15:00

16:15:00

Description

Onbekend

Some tekst

The solution

Search string

6

If you need more info, just let me know.

Kind regards,

Björn