Forum Discussion

Jonas_Jonas's avatar
Jonas_Jonas
Frequent Visitor
9 years ago

Rest: Extract a List which contains Records

Hello,

 

I'm currently working with REST as datasource for a small project.

 

That's the rest result:

 

 "customfield_1": [
          {
            "self": "https",
            "value": "Dev",
            "id": "2"
          },
          {
            "self": "https",
            "value": "Project",
            "id": "3"
          },
          {
            "self": "https",
            "value": "Care",
            "id": "4"
          }
        ],

 

So I have an atttribute which contains a list of records. For our project I want to know the three values of the attribute "customfield_1". But if I expand the attribute "customfield_1" Power Bi copies the entire line three times once with "Care", once with "Project" and once with "Dev". Which means if I have an additional attribute called "Priority_Value" with the value 5 and I create a graphic Power Bi shows us a priorty_value of 15 because it acts like the three instances are separate but in fact are the same line.

 

Does someone have a solution for that problem? I would like to combine the three values in one cell and not in three seperate instances of the same result.

 

So it should be like this:

 

customfield_1: "Dev; Project; Care"

and not like this:

 

customfield_1: "Dev"
customfield_1: "Project"
customfield_1: "Care"

 

To expand on the problem:

 

That’s how I get the data:

 

ID

Cash

Customfield_1

Check

1

10000

Dev

Yes

1

10000

Project

Yes

1

10000

Care

Yes

 

That’s how it should be:

ID

Cash

Customfield_1

Check

1

10000

Dev; Project; Care

Yes

 

Best Regards.

Jonas_Jonas

2 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi Jonas_Jonas,

     

    To transfer data structure from
    customfield_1: "Dev"
    customfield_1: "Project"
    customfield_1: "Care"

    to
    customfield_1: "Dev; Project; Care"

    You can use pivot and unpivot featrure in Power BI. Turning columns to rows, or rows to columns is easy with Power Query and Power BI. Here is a document which describes basic pivot and unpivot in Power BI.

    http://radacad.com/pivot-and-unpivot-with-power-bi

     

    Regards,

    Charlie Liao

    • Jonas_Jonas's avatar
      Jonas_Jonas
      Frequent Visitor

      Hi,

       

      i added some information to the problem description

       

      Best regards.

      Jonas_Jonas