API query- how to get just the object with a value in a list of records

[danRu]

Hi,

I'm new to Power Query and would appreciate any assistance on this.

I'm querying an API endpoint- one of the columns I'm getting back contains the data type 'List'.

Inside each list is one or more records (There is no way of knowing how many there will be as it changes for each line).

There will be be one record that has a key value pair with a value. The others will have a value of 'null' for that key.

How do I extract just that value for the given key where there is a value and disregard the other records for which the value is 'null'?

Thank you so much!

Regards,

Daniel

[PhilipTreacy]

No worries @danRu 

Can you share your file and provide an example (image or table) of the result you want?

Regards

Phil

[danRu]

Hi @PhilipTreacy ,

Thank you for this. I need to put something together as I can't send you the original file.

Will revert when I have it. Thank you again.

Best,

Daniel

[PhilipTreacy · Accepted solution]

Hi @danRu 

Yes that does help a bit thanks but I'm still not 100% sure about your data structure because you say you have a column of Lists and inside the Lists are Records and inside those Records is JSON?  How many Records are inside each List?

It may not matter if you try this.  With your column of Lists, double click the double headed arrow in the column header to extract data from the lists.

This should give you a column of records

You can again click the double headed arrow to extract data from the records, but you only want to extract credit 

You should get a mixture of records and null, with the records containing the data you want

use the double headed arrow to extract the data, then filter null from any column to get rid of null rows

Of course I'm doing this in isolation to any other data so I'm not sure if this will work for you or if it will remove rows you want.

Here's my full example query for you to examine

let
    Source = 
    { 
        [id = 1, person_id = 1, credit = null, status = "hired"] , [id = 2, person_id = 2, credit = [id = 22 , first_name = "Bob", last_name = "Smith", emp_id = 19], status = "hired"] , [id = 3, person_id = 3, credit = null, status = "hired"] , [id = 4, person_id = 4, credit = null, status = "hired"],
        [id = 11, person_id = 11, credit = null, status = "hired"] , [id = 12, person_id = 12, credit = null, status = "hired"] , [id = 31, person_id = 31, credit = null, status = "hired"] , [id = 14, person_id = 14, credit = [id = 1 , first_name = "Jane", last_name = "Smith", emp_id = 99], status = "hired"] 
    },
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"credit"}, {"credit"}),
    #"Expanded credit" = Table.ExpandRecordColumn(#"Expanded Column1", "credit", {"id", "first_name", "last_name", "emp_id"}, {"id", "first_name", "last_name", "emp_id"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded credit", each ([id] <> null))
in
    #"Filtered Rows"

If this doesn't work we can try probing the Lists and Records for the  data you want but I need to know the exact structure of the data.

Regards

Phil

[danRu]

Hi @PhilipTreacy ,

Thank you so much for your expertise! This helps me tremendously.

I'm not sure I understand the data structure entirely myself so I apologize if it's unclear.

I'm building a report which needs this application's 'credit' key as a column heading with the value if there is one. Some lines have one or more applications and their respective 'credit' keys may or may not have a value. If none of the applications (per line) have a value, it is null. If there is a value for 'credit' in one of the applications then it should show value.

The 'value' should be the 'first_name' key to present as the value for 'credit'. Sorry, I didn't make that clear.

So if there's a value and first_name is 'John' then 'John' should be displayed as the value under the 'credit' column.

I'm also trying to figure out how to solve my problem. Perhaps I'm going about it wrong (above) as I'm seeing that there can now be multiple obkec.

Maybe I need to try and select the application I want according to this criteria.

If one or more of the applications has a value in the 'credit' column, extract the record (application) which has a value for 'credit' and where the status is equal to "hired". If not then the value is null?

Is it possible to do this? 

Thanking you again!

Daniel

[PhilipTreacy]

Hi @danRu 

Can you share what the data looks like?  Do you know the name of the key that contains the value you want?  Or do you have to search though all records looking for it?

Regards

Phil

[danRu]

Hi @PhilipTreacy ,

Many thanks for your reply.

The api returns this JSON response per line. So this is one of the 'lists' inside my query column:

{

    "id": 1,

    "first_name": "John",

    "last_name": "Doe",

    "company": "Contoso.",

    "title": "Manager",

    "created_at": "2015-12-26T09:54:12.028Z",

    "applications": [

        {

            "id": 1,

            "person_id": 1,

            "credit": null,

            "status": "rejected"

        },

        {

            "id": 2,

            "person_id": 2,

            "credit": null,

            "status": "rejected"

        },

        {

            "id": 3,

            "person_id": 3,

            "credit": {

                "id": 9,

                "first_name": "Jane",

                "last_name": "Doe",

                "emp_id": null

            },

            "status": "hired"

        },

        {

            "id": 25,

            "person_id": 26,

            "credit": null,

            "status": "hired"

        }

    ]

}

I want to get the "credit" key out of this array of objects (applications). In this scenario, I expect only one application to have a value for "credit". For the others the value for credit should be null. How do I get just the value for the application where it's 'credit' has a value?

Here's how it looks in power query:

I click on list to get the underlying records and click on a record to display the above JSON below.

Does this help?

I understand (please correct me if I'm wrong) that 'lists' are Arrays and 'Records' are objects? 

Thanks again.

Regards,

Daniel