dealing with a list of records (and null values)

[IanDavies]

I am importing some data using an API, it comes as JSON and I can get the data I need from that until I get to the group(s) that each item is a member of.

each item can be a member of zero or more groups. if that number is zero it appears to enter a null value, for the others it has a record for each item with name & id.

this is what each list holds:

each record has two fields

except when its not in any groups and then it puts a null value in the row

when I try to expand that, either by using the GUI or a custom column it errors because it cant transform type null.
In other (similar) circumstances I would replace the null value, but I don't get that option.

Is there any method to expand this column (groups) to show its values without losing rows?

[Chewdata]

hey,

Another possible solution is to create a new column that inserts a list with a default record in al the empty spaces. 

= Table.AddColumn(#"Replaced Value", "NewList", each [groups] ?? {[ID = 999999, Name = "Chewdata"]})

OR 
transform current column:
Table.ReplaceValue(#"Changed Type",null,[ID = 999999, Name = "Chewdata"],Replacer.ReplaceValue,{"groups"}

After that you can filter out the rows that have ID = 999999

[Anonymous · Accepted solution]

On the contrary--if you extract to new rows, you will see that you still get only one row--a record, which was all that was in each list. Now you can extract the record values, instead of list items.

Just give it a try--it works.

--Nate

[IanDavies]

Ok, it works well enough for my purpose.

A word of warning to anybody who comes across this if the column has multiple records it will create multiple rows (1 per record)

[IanDavies]

in the screenshot the exaples are only in one group but there are some in multiple groups, I cant dispaly them  because it has the error and wont let me past row 86 (despite there being 1300 rows).

I'll try your method and see if I can demonstrate it.

[IanDavies]

like this:

Two records in the list

making two rows for this item:

Theer aren't many but it does happen.

[Anonymous]

On your Groups column, just click "Expand to New Rows" instead of "Extract Values".  This will expand yor lists to a single record per row.  After that, you can safely use the arrows at the top of the column to expand the values.

--Nate

[IanDavies]

This is an inventory, having multiple rows for the same item would be very difficut to manage unless there were a way to later merge them.

I cant think of one which is why I wasn't taking this option in the first place. 

I wish it would just ignore null values, if there is nothign there, there is nothing to expand/convert so why try?

[Omid_Motamedise]

Select that column, right click on it and pick replace command, then use the setting provided in the next image for replacing null values with a list.

[IanDavies]

tried that ... I noted this didnt work in my question:

this is what I get - note the lack of Replace Values

[Omid_Motamedise]

Perform the replace operation on a different column first. Then, go to the formula bar and update the formula by changing the column name in the fourth argument of Table.ReplaceValue to the desired column.

[PhilipTreacy]

@IanDavies 

Hi Ian,

I'm not 100% following you. When you refer to an item I don't know what that is.

You say each record has 2 fields except when its not in any groups and then it puts a null value in the row.

The image after this statement shows a column of Lists with a null value.  Where do the Records come in?

Is the issue that the groups column contains Lists or null values?  And each List contains a Record, which itself contains 2 values?

So what is the exact issue?  You want to get rid of the nulls in the groups column? 

You could create a Custom Column with this

if [groups] is null then "" else [groups]

then filter the blank values and extract your lists.

Or you could extract the lists and then remove rows with errors.

If that's not what you want please restate the issue clearly and show what you want as a final result.

Regards

Phil

[IanDavies]

---

See reponse below in blue

@PhilipTreacy wrote:

@IanDavies 

Hi Ian,

I'm not 100% following you. When you refer to an item I don't know what that is.

each item = each row in the query.

You say each record has 2 fields except when its not in any groups and then it puts a null value in the row.

The image after this statement shows a column of Lists with a null value.  Where do the Records come in?

each list in [groups] has a record with two fields - see the additional screenshots in the original post

Is the issue that the groups column contains Lists or null values?  And each List contains a Record, which itself contains 2 values?

yes

So what is the exact issue?  You want to get rid of the nulls in the groups column? 

I want to be able to convert the lists to data that can be read but that fails because of the null values. I would normally replace the null values but I can't do that in the GUI

You could create a Custom Column with this

if [groups] is null then "" else [groups]

So I did this and it has created a new column

then filter the blank values and extract your lists.

The filter only has Select all or Blanks

 

Also I cant expand the new custom column

Or you could extract the lists and then remove rows with errors.

not sure what you mean by "extract the lists"

If that's not what you want please restate the issue clearly and show what you want as a final result.

I want this but without errors - two columns with the values from the list/records of the [groups] column

I created this example by creatng a custom column using Table.FromRecords([groups]) then expanding the custom column, but when I do this I get an error appear in EVERY column at the bottom of the previewed data, which isnt there in previous steps

Is this clearer?

Ian

---

[PhilipTreacy]

Hi @IanDavies 

Download PBIX file with examples below

The file I created shows both examples I described.

If you Extract Values

you get Errors where there were nulls

You can then right click the column header and Replace Errors with nothing (blanks)

then filter out blanks

The other way is to create a Custom Column with this

= if [groups] is null then "" else [groups]

You can then Remove Empty cells (my bad - I wrote filter blanks earlier)

This leaves you with just Lists which can be expanded

Regards

Phil

[IanDavies]

Thank you your time, I wont have a chance to loo at this again until next week but I will look at both solutions. My concern with them both is that I will lose rows of data simply because they arent a member of a group, which is something I need to count\highlight in my dashboard.

I tried an amended version of your if statement but got a different error entirely:

= if [groups] is null then List.Generate(() => [ name = "none", id = 0],each [id] = 0,each [id = [id] + 1])else [groups]

 This was an attenpt to add a list with one record in it that had the same fields as those imported. It appeared to work and if you look at the previously null records they do now have a list with one record in them but I am now  getting this error:

I'll have a look at your suggestions and see whether they work or not.