How to expand a table column, each row having a list, which has 17 records, whose values are lists

[Anonymous]

My source is a json file which contains test cases from HP ALM Quality center. Each test case has 17 records with a Name and a value. The value of the record is again a list with either no element or just one element.

One test case looks like this:

{"Fields":[{"Name":"owner","values":[{"value":"myname"}]},{"Name":"user-41","values":[{"value":"MyProject"}]},{"Name":"description","values":[{}]},{"Name":"exec-status","values":[{"value":"Failed"}]},{"Name":"last-modified","values":[{"value":"2020-10-2009:45:56"}]},{"Name":"user-09","values":[{"value":"Approved"}]},{"Name":"subtype-id","values":[{"value":"MANUAL"}]},{"Name":"creation-time","values":[{"value":"2020-09-16"}]},{"Name":"name","values":[{"value":"testing whatever"}]},{"Name":"user-01","values":[{"value":"TC001"}]},{"Name":"user-12","values":[{"value":"Fully automated"}]},{"Name":"id","values":[{"value":"542239"}]},{"Name":"parent-id","values":[{"value":"81602"}]},{"Name":"user-43","values":[{"value":"High"}]},{"Name":"user-10","values":[{"value":"Functional"}]},{"Name":"user-15","values":[{"value":"Yes"}]},{"Name":"status","values":[{}]}]}

Respectivily in Power query I have a column with rows, which are lists. A row has a list with the records as above but I cannot find a way to extract the columns and create new 17 columns each containing the values from each record.

Table.ExpandListColumn will provide the records all in the same column as before, one after the other and I will get 17 times the number of initial rows in the column.

What I need at the end is a table with columns, the names of each record e.g Column.owner, Column.user-41,...

In this table each row should have the final values of the records of one test cases e.g. myname, Myproject,...

Any ideas on how to do this please?

[Anonymous · Accepted solution]

After finding this thread Power Query - Get a Table from a Column of Lists (list of lists) and the answer provided by @Michal_cwiok and after playing around for some hours, following has worked and can be used if anyone has the same issue.

Create a helper function by creating a new query, name it as you wish and paste the following:

(row as list) =>

let
#"Converted to Table1" = Table.FromList(row, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Name", "values"}, {"Name", "Values"}),
#"Expanded Column1.values" = Table.ExpandListColumn(#"Expanded Column2", "Values"),
#"Expanded Column1.values1" = Table.ExpandRecordColumn(#"Expanded Column1.values", "Values", {"value"}, {"Values.value"}),
#"Transposed Table" = Table.Transpose(#"Expanded Column1.values1")
in
#"Transposed Table"

Then create your main query, name it as you wish and paste the following after changing your source to your specific data source:

let
#"TEST_STATUS_QC_FullDump (2)" = Json.Document(Web.Contents(Jenkins_Url_Qcdump/"tests-limfields-.0.json")),
entities = #"TEST_STATUS_QC_FullDump (2)"[entities],
#"Converted to Table" = Table.FromList(entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Fields", "Type", "children-count"}, {"Column1.Fields", "Column1.Type", "Column1.children-count"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"Column1.Fields"}),
#"Column1 Fields" = #"Removed Other Columns"[Column1.Fields],
First_try = List.Transform(#"Column1 Fields", each QC_helper(_)),
#"Converted to Table1" = Table.FromList(First_try, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandTableColumn(#"Converted to Table1", "Column1", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}, {"Column1.Column1", "Column1.Column2", "Column1.Column3", "Column1.Column4", "Column1.Column5", "Column1.Column6", "Column1.Column7", "Column1.Column8", "Column1.Column9", "Column1.Column10", "Column1.Column11", "Column1.Column12", "Column1.Column13", "Column1.Column14", "Column1.Column15", "Column1.Column16", "Column1.Column17"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Column2", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"owner", type text}, {"user-41", type text}, {"description", type text}, {"exec-status", type text}, {"last-modified", type text}, {"user-09", type text}, {"subtype-id", type text}, {"creation-time", type text}, {"name", type text}, {"user-01", type text}, {"user-12", type text}, {"id", type text}, {"parent-id", type text}, {"user-43", type text}, {"user-10", type text}, {"user-15", type text}, {"status", type text}})
in
#"Changed Type"

Finally it works! This is what I get now:

Thank you all for your help!

[watkinnc]

Hey there.  Once you have your list column ready, this will work: instead of choosing "Expand to new rows", choose "Extract values", and select "Comma" as the delimiter.  Now, you can split the column by delimiter; this will make the columns that you need.  Here is the sample code, just replace my table and column names.  If you use the GUI for the split function, it will expand only to the number of columns that equals the number of items in your list.

Split = Table.SplitColumn(ExtractedValues, "ListColumn", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ListColumn.1", "ListColumn.2", "ListColumn.3", "ListColumn.4", "ListColumn.5", "ListColumn.6", "ListColumn.7", "ListColumn.8", "ListColumn.9", "ListColumn.10", "ListColumn.11", "ListColumn.12", "ListColumn.13", "ListColumn.14", "ListColumn.15", "ListColumn.16", "ListColumn.17", "ListColumn.18", "ListColumn.19", "ListColumn.20", "ListColumn.21", "ListColumn.22", "ListColumn.23", "ListColumn.24", "ListColumn.25", "ListColumn.26", "ListColumn.27", "ListColumn.28", "ListColumn.29", "ListColumn.30", "ListColumn.31", "ListColumn.32", "ListColumn.33", "ListColumn.34", "ListColumn.35", "ListColumn.36", "ListColumn.37", "ListColumn.38", "ListColumn.39"})

---Nate

[Anonymous]

@hep,

Thank you for your proposal but it doesn't seem to work. I get following error:

Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]

I guess that I get the error because my list contains records and not text.

[Fowmy]

@Anonymous 

If you can share a sample file with the same structure, I can try it out.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy 

This is a sample of a json file with 2 test cases:

{"entities":[{"Fields":[{"Name":"owner","values":[{"value":"nyname"}]},{"Name":"user-41","values":[{"value":"myproject"}]},{"Name":"description","values":[{}]},{"Name":"exec-status","values":[{"value":"Failed"}]},{"Name":"last-modified","values":[{"value":"2020-10-20 09:45:56"}]},{"Name":"user-09","values":[{"value":"Approved"}]},{"Name":"subtype-id","values":[{"value":"MANUAL"}]},{"Name":"creation-time","values":[{"value":"2020-09-16"}]},{"Name":"name","values":[{"value":"my test sesciption 1"}]},{"Name":"user-01","values":[{"value":"TC001"}]},{"Name":"user-12","values":[{"value":"Fully automated"}]},{"Name":"id","values":[{"value":"542239"}]},{"Name":"parent-id","values":[{"value":"81602"}]},{"Name":"user-43","values":[{"value":"High"}]},{"Name":"user-10","values":[{"value":"Functional"}]},{"Name":"user-15","values":[{"value":"Yes"}]},{"Name":"status","values":[{}]}],"Type":"test","children-count":0},{"Fields":[{"Name":"owner","values":[{"value":"tester2"}]},{"Name":"user-41","values":[{"value":"myproject"}]},{"Name":"description","values":[{}]},{"Name":"exec-status","values":[{"value":"No Run"}]},{"Name":"last-modified","values":[{"value":"2020-09-24 11:18:23"}]},{"Name":"user-09","values":[{"value":"Approved"}]},{"Name":"subtype-id","values":[{"value":"MANUAL"}]},{"Name":"creation-time","values":[{"value":"2020-09-24"}]},{"Name":"name","values":[{"value":"my test description 2"}]},{"Name":"user-01","values":[{"value":"TC002"}]},{"Name":"user-12","values":[{"value":"Manual"}]},{"Name":"id","values":[{"value":"542623"}]},{"Name":"parent-id","values":[{"value":"81635"}]},{"Name":"user-43","values":[{"value":"High"}]},{"Name":"user-10","values":[{"value":"Functional"}]},{"Name":"user-15","values":[{"value":"Yes"}]},{"Name":"status","values":[{}]}],"Type":"test","children-count":0}],"TotalResults":2}

I am not interested in columns "Type":"test" and"children-count" so you can discard them.

Thank you very much!

[Fowmy]

@Anonymous 

Hope you need the following output? Paste the code in a blank Query, in the Advanced Editor and check the steps and modify as per your actual table,  Change the file location.

let
    Source = Json.Document(File.Contents("C:\Users\Fowmy\OneDrive\BI\PBICommunity\samplefile.json")),
    entities = Source[entities],
    #"Converted to Table" = Table.FromList(entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Fields"}, {"Fields"}),
    #"Expanded Fields" = Table.ExpandListColumn(#"Expanded Column1", "Fields"),
    #"Expanded Fields1" = Table.ExpandRecordColumn(#"Expanded Fields", "Fields", {"Name", "values"}, {"Name", "values"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Fields1", "Custom", each try [values]{0}[value] otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"values"})
in
    #"Removed Columns"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy, 

Thank you very much for your reply. I am afraid that what I would like to have is a bit more complicated.

This is the table I would like to have if possible.

owner         user-41        description ......   user-15    status

myname     myProject    null             ......    Yes          null

Tester2       myProject    null             ......    Yes          null

With headers tha names of the fields and each row having the values of the expanded lists of the records of each object.

Do you think this is possible please?

[Fowmy]

@Anonymous 

It's possible to transform it that way when you have some consistency in your data.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy,

Can you please elaborate? Do you think that data have no consistency?

[Fowmy]

@Anonymous 

If you look at the output that got generated from my code, it shows that there is an issue with the data, please check with your source data.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn