Forum Discussion

JC2022's avatar
JC2022
Helper III
3 years ago
Solved

Record and List to Table

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?

Need Help
Show and Tell
  • ppm1's avatar
    ppm1
    3 years ago

    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

     

3 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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's avatar
      JC2022
      Helper III
      3 years ago

      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's avatar
        ppm1
        Solution Sage
        3 years ago

        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