Power Query - Get a Table from a Column of Lists (list of lists)

[Anonymous]

Using a rest api and I now have a column where each row is a list of records.

If I expand that column my only options are 'Expand to new rows' or 'Extract Values'. 

I don't want new rows, because the records in each list comprise a 'row' of data.  And I can't figure out how to 'extract' the record value type.

Any help is greatly appreciated!

[Anonymous]

I'm going to try the suggestion in this ticket as the source data in my get call is also json data. 

http://community.powerbi.com/t5/Desktop/Expand-colum-with-lists/m-p/52655/highlight/true#M21187 

[Anonymous]

The above solution still didn't work b/c the id column gets duplicated when I expand the column that has a list of records for each row.

What I tried was having a different query for the column headers and the values, as such:

Here's what it looks like before and after I try to expand the rows column:

After + something to express my feeling at this point:

A

As you can see 'Expanding to new rows' duplicates the id.  So I'm pretty much in the same position I was to begin with. 

Thanks for reading this far & for any help you can provide.

[Michal_cwiok]

Could you tell me, what the expected format is? What table/dataset do you want to get after the transformation?

[Anonymous]

ideally it would look like this: 

DeviceID	Device Display Name	TagID	Column Name	Timestamp	Data Value
195	Leon Central Facility	8640	Powerfluid Oil Specific Gravity	1/1/2018 6:00	0.8049
195	Leon Central Facility	7893	Oil Tank 01 Interface Level	1/1/2018 6:00	0.87

This represents the 'columns' query:

And rows 1-11 of the rows query:

In the posts above I was attempting to add a 'columnID' column to the rows table (e.g. 0,1,2,3,4 or 5) that I could then use to add the column headers to the rows table via calculated column and then pivot the data, but to no avail. 

If you know of a way to extract 'Record' type data from a list with out adding new rows that would be helpful.  Or really anything to get to the example above.  I don't really care how I get there as long as I reproduce it in subsequent REST calls.

It's returning the data in JSON format, which is why all of my 'rows' of data end up in (1) column (see 'rows' query | first image above)

[Michal_cwiok]

How I would tackle the problem is creating a custom fuction to extract data from each row. Would it be possible if you shared a sample code? Thanks

[Anonymous]

Unfortunately, no.  I can't share the data.  Thanks for your suggestion though @Michal_cwiok.  I'll take a look at the custom functions.  Maybe there's a way to use that to extract the records for each 'rows' list into it's own new column. 

[Michal_cwiok]

This particular case is hard to replicate, hence the question. Perhaps you could paste the JSON with anonymized data? Two-three rows should be enough.

If not, my suggestion is:

1. If possible download one row and do all the transformation. Save this as a custom function.

2. Apply this function for each row using List.Transform(), I wrote about it briefly here.

Let me know, if you have any other questions. Thanks

[Anonymous]

@Michal_cwiok

Okay, here is some sample json with just (3) rows of anonymized data:

I'll check your post.  Thanks again!

{
    "reportName": "Tabular Report 1",
    "id": "776",
    "tables": [
        {
            "id": "0",
            "tableName": "New Section 0",
            "columns": [
                {
                    "id": "0",
                    "caption": "DeviceID",
                    "aggFunc": 0,
                    "dataType": 0
                },
                {
                    "id": "1",
                    "caption": "Device Display Name",
                    "aggFunc": 2,
                    "dataType": 0
                },
                {
                    "id": "2",
                    "caption": "TagID",
                    "aggFunc": 0,
                    "dataType": 0
                },
                {
                    "id": "3",
                    "caption": "Column Name",
                    "aggFunc": 0,
                    "dataType": 0
                },
                {
                    "id": "4",
                    "caption": "Timestamp",
                    "aggFunc": 0,
                    "dataType": 3
                },
                {
                    "id": "5",
                    "caption": "DataValue",
                    "aggFunc": 0,
                    "dataType": 2
                }
            ],
            "rows": [
                {
                    "cells": [
                        {
                            "val": "195"
                        },
                        {
                            "val": "Device1"
                        },
                        {
                            "val": "8640"
                        },
                        {
                            "val": "Tag1"
                        },
                        {
                            "val": "6/7/2018 5:00:00 AM +00:00"
                        },
                        {
                            "val": "0.7761"
                        }
                    ]
                },
                {
                    "cells": [
                        {
                            "val": "195"
                        },
                        {
                            "val": "Device1"
                        },
                        {
                            "val": "7893"
                        },
                        {
                            "val": "Tag2"
                        },
                        {
                            "val": "6/7/2018 5:00:00 AM +00:00"
                        },
                        {
                            "val": "19.33"
                        }
                    ]
                },
                {
                    "cells": [
                        {
                            "val": "195"
                        },
                        {
                            "val": "Device1"
                        },
                        {
                            "val": "7892"
                        },
                        {
                            "val": "Tag3"
                        },
                        {
                            "val": "6/7/2018 5:00:00 AM +00:00"
                        },
                        {
                            "val": "19.33"
                        }
                    ]
                },
            ]
        }
    ]
}

[Michal_cwiok · Accepted solution]

@Anonymous

I think I got it. Please save this as test.json in C:\ drive.

Create this custom function:

(row as list) =>

let

    #"Converted to Table1" = Table.FromList(row , Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"val"}, {"Column1.val"}),
    #"Transposed Table" = Table.Transpose(#"Expanded Column2")
in
    #"Transposed Table"

And paste the whole transformation:

let
    Source = Json.Document(File.Contents("C:\test.json")),
    tables = Source[tables],
    #"Converted to Table" = Table.FromList(tables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "tableName", "columns", "rows"}, {"Column1.id", "Column1.tableName", "Column1.columns", "Column1.rows"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.id", "Column1.tableName", "Column1.columns"}),
    #"Expanded Column1.rows" = Table.ExpandListColumn(#"Removed Columns", "Column1.rows"),
    New =  #"Expanded Column1.rows"[Column1.rows],
    New_table =Table.FromList(New, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column3" = Table.ExpandRecordColumn(New_table, "Column1", {"cells"}, {"Column1.cells"}),
    To_list = #"Expanded Column3"[Column1.cells],
    First_try = List.Transform(To_list, each Query1(_)),
    #"Converted to Table2" = Table.FromList(First_try, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column4" = Table.ExpandTableColumn(#"Converted to Table2", "Column1", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1.Column1", "Column1.Column2", "Column1.Column3", "Column1.Column4", "Column1.Column5", "Column1.Column6"})
in
    #"Expanded Column4"

Sorry for the code quality, but I am in the middle of something. Nonetheless I wanted to deliver a quick answer for you. Please analize this and ask questions if needed. What I got:

[Anonymous]

Hey @Michal_cwiok,

I really appreciate you taking time to help.  I'm still having trouble, however, probably due to my lack of knowledge in M and using custom fucntions.  I've read your post a couple of times and tried this a few ways.  

For the custom function I created a blank query ('Query1') and pasted your coded:

In the 'startHere' query that I have I then open 'Advanced Editor and copy/pasted everything:

But when the function is envoked in the code an 'Expression.Error' is thrown:

Expression.Error: We cannot convert the value "(row as list) =>

le..." to type Function.
Details:
    Value=(row as list) =>

let

    #"Converted to Table1" = Table.FromList(row , Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"val"}, {"Column1.val"}),
    #"Transposed Table" = Table.Transpose(#"Expanded Column2")
in
    #"Transposed Table"
    Type=Type

[Michal_cwiok]

Hi, I think I know what the problem is. It says right there:

We cannot convert the value "(row as list) =>

le..." to type Function

You pasted the function as text. To solve it, please open Advanced Editor and paste the code again. There should be not quotation marks at the beginning or end. Let me know of the result!

Thanks

[Anonymous]

@Michal_cwiok, 

Well what I ended up having to do was just manually type the whole function and then it seemed to work no problem.

THANK YOU SO MUCH for all your help!  I really appreciated it!!