Help with expanding json list columns

[jereaallikko]

Hi all,

I need help with modifying columns that contains List->Record.

I am having a table of two columns : Id and Column1 and 82 rows. As shown below, Column1 contains List which needs to be expanded. 

After expanding it to new rows, it creates a records and adds 38 rows = 180rows. Under the record, there are 2 columns, ID and Label. The problem is, that Power Query is duplicating the IDs that contains more than 1 Label.

What I am trying to do, is to form the columns in a way, that there are no duplicate IDs, (caused by more than 1 Label). Instead, how can I form it in a way, that if a ID contains more than 1 Label, the labels are in the same row.

For example;

ID567205 contains 2 Labels; 1.1.20A_L3INT_NUM & 1.1.20A_W_0.1MP_NUM

and the query looks like this

ID		Column1.label
ID567205		1.1.20A_L3INT_NUM
ID567205		1.1.20A_W_0.1MP_NUM

But I would like it to look like this

ID		Column1.label
ID567205		1.1.20A_L3INT_NUM, 1.1.20A_W_0.1MP_NUM

PS, if I try to expand the List with Extract values with comma, it shows Error and tells;

Expression.Error: We cannot convert a value of type Record to type Text.

I have tried to change the data type to any/text/whole number etc. but nothing seems to work. 

Feel free to ask more information if my explination was not clear enough.

Best regards,

Jere

[jereaallikko · Accepted solution]

Hi all,

Thanks for your effort and replies. I found a solution to my issue from here : 

https://community.powerbi.com/t5/Power-Query/Multi-Dimensional-Json-file-to-Table/td-p/113667

In the end I combined the extracted columns into one.

[v-alq-msft]

Hi, @jereaallikko 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may add a new step as below.

Table.Group(Source,"ID",{"Values",each Text.Combine([Value],",
")})

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jereaallikko]

Hi @v-alq-msft 

Thanks for your reply. This would have probably been the easiest way to solve the problem. But as I applied the code to my dataset, an error occurs:

Any suggestions?  

[Jimmy801]

Hello @jereaallikko 

did you check my solution?

BR

Jimmy

[jereaallikko]

Hello @Jimmy801 

Yes, I did. It would also work, if the dataset and the amount of rows were be smaller. As the dataset contains over 80 rows, the manual work with that solution would be too much. Additionally, each time new data is rolling in, I should make modifications with advanced editor according to the new data.

Thanks anyways for the solution. If nothing more simple appears, this is probably the solution I must use.

Do you have any more simple solution to suggest?

Br, Jere

[Jimmy801]

Hello @jereaallikko 

i still don't get it. The question was how to create a measure to show the result you need. What you mean by manual work? What you have to do for sure is to connect your original dataset to my example.

BR

Jimmy

[jereaallikko]

Hi @Jimmy801 

If I have understood your solution correctly, I mean that it would take forever to modify it, since the dataset contains tens of rows.

If you have an email where I can send the original dataset and pbi query, I could do it, so we could get common understanding of the issue. 

Br, Jere

[Jimmy801]

Hello @jereaallikko 

in my example I created manually a dataset to show you a solution with DAX (measures etc.). When you need to load aaaa llloooot more data, then connect power query with your dataset, and apply afterwards my solution in DAX. ----->>>> wrong threat...

so... this now is valid for your request. The code I supplied basically transform you "list"-column to extract data from a record. you just have to apply the main step in my query to your code

Hope I was now clear enough 🙂

BR

Jimmy

[Anonymous]

[Fowmy]

@jereaallikko 

Can you share some sample data 
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

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

YouTube, LinkedIn

[Jimmy801]

Hello @jereaallikko 

I don't know exactly how this list is structured, but I suppose it contains records with 2 fields. one called "id", the other one "label". If it's like this, this transformation should work out. Check out this code

let 
    YourTable = #table(type table[ID=  text, Column1=  list ], {{"ID12345", {[id= 1234, label= "testlabel"], [id= 1237, label= "testlabel7"]}},{"ID12346", {[id= 1235, label= "testlabel1235"]}}}),
    TransformListOfRecordsToText = Table.TransformColumns
    (
        YourTable,
        {
             {
                 "Column1",
                 (listint)=>Text.Combine(List.Transform(listint, each  Record.Field(_,"label")), "#(lf)")
             }
        }
    )
in
    TransformListOfRecordsToText

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy