Forum Discussion

jereaallikko's avatar
jereaallikko
Helper III
5 years ago
Solved

Help with expanding json list columns

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

Need Help

11 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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's avatar
      jereaallikko
      Helper III
      5 years ago

      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?