Record and List to Table

[JC2022]

Hi,

Can you please help me with a json datasource. I have lists and records as shown below, but I would like to create a Table from it.

1. See the column data.items. It is a column with "Record" which I can expand. Some expanded columns have values (which is great), but some have Lists.

2. data.items.id is an expanded column with values, but data.items.autoProperties is a column with "List".

3. I have expanded column data.items.autoProperties with "Expand to new rows". That's why there are multiple rows for data.items.id = 4 and 135 etc. Some data.items.id have more rows than others, depending on the amount of records in the list.

In data.items.autoProperties it still shows "Record".

4. If I expand column data.items.autoProperties to data.items.autoProperties.name and data.items.autoProperties.value this will be the result.

5. But I would like to have data.items.autoProperties.name als columns and data.items.autoProperties.value als the value. With only one row for a unique data.items.id. Can you guys please help me?

[ppm1]

Starting at your last picture, you should be able to pivot on the name columns and use the value column as the values. Select the name column and choose Pivot Column on the Transform tab. Have you tried that?

It should also be possible to use List.Transform to turn your list of records into a record (w/o having to initially expand the list and then pivot).

Pat

[JC2022]

Hi @ppm1,

this creates values of 1 and 0, depending if there is data (1) and when there is no data (0). But not the actual data itself.

Can you write me the code of your other solution (List.Transform), because I have tried some things but it didn't work out. I was getting all errors.

[ppm1 · Accepted solution]

Make sure you select your value column for the values in your pivot and choose "Don't Aggregate".

Or here is an expression you can put in a custom column. Replace Custom with your column that has your list of records (before you expand, pivot, etc.).

let 
inputlist = [Custom],
fields = List.Transform(inputlist, each _[name]),
values = List.Transform(inputlist, each _[value]),
result = Record.FromList(values, fields)
in 
result

Pat