Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jereaallikko
Helper III
Helper III

Help with expanding json 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. 

PBI.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

Capture.PNG

 

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

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hi @jereaallikko ,

if my understanding is correct you have to tweak the expansion code a bit like so:

 

let
  Source = #table(
      {"ID", "Column1"}, 
      {{"ID1", {[id = 12500, label = "58T"], [id = 13500, label = "13A"]}}}
    ),
  #"Extracted Values" = Table.TransformColumns(
      Source, 
      {
        "Column1", 
        each Text.Combine(
            List.Transform(_, (x) => Text.Combine({Text.From(x[id]), Text.From(x[label])}, ", ")), 
            ", #(lf)"
          ), 
        type text
      }
    )
in
  #"Extracted Values"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

1 REPLY 1
ImkeF
Super User
Super User

Hi @jereaallikko ,

if my understanding is correct you have to tweak the expansion code a bit like so:

 

let
  Source = #table(
      {"ID", "Column1"}, 
      {{"ID1", {[id = 12500, label = "58T"], [id = 13500, label = "13A"]}}}
    ),
  #"Extracted Values" = Table.TransformColumns(
      Source, 
      {
        "Column1", 
        each Text.Combine(
            List.Transform(_, (x) => Text.Combine({Text.From(x[id]), Text.From(x[label])}, ", ")), 
            ", #(lf)"
          ), 
        type text
      }
    )
in
  #"Extracted Values"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors