Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

How to expand two columns with two lists

Hi everyone,

 

I am getting the data from an API. The API response is as follows:

So, when I read the data in Power BI, it looks like this:

Then, I expanded series to get a record and again to get a list. It looks like the previous picture.
When I try to expand the first column, I get this:


My problem is when I try to expand the second column, it assigns for each value of the list per row the same value of the series.data.

For example: If the list in the first row has 7 values, when it is expanded for these 7 values, it places the value 0. But what I want is for the first value in the list to keep the 0 in series.data, for the second value in the list to keep the 1, and so on.

 

Picture of the problem:

What I need:

Note: I had a solution of duplicating the API request to get another table, put an index and merge. But it does not work because of the amount of API requests.
Could you help me to get a solution directly in the same table, not generating another one?

Thanks to all!

 

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Looks like you could use List.Zip to make a custom column, like

     

    =Table.AddColumn(PriorStepOrTableName, "Values", each List.Zip([x_axis.labels], [series]))

     

    Then remove the other list columns, expand the new column, expand again, then split by the comma.

     

    --Nate

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi, thanks for your help!

      I repeated the steps you gave me and I got this:

      This is a really good approximation. I want to know how to split it? Because it doesn't have commas.

  • Anonymous 

    Please share your source data file.

    Save in Google Drive and share link here:

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Fowmy,


      Sorry, but this is something related to the company where I work with, so I can't share the files.