Forum Discussion
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
- AnonymousNot 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
- AnonymousNot 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.
- FowmySuper User
Anonymous
Please share your source data file.
Save in Google Drive and share link here:- AnonymousNot applicable
Hi Fowmy,
Sorry, but this is something related to the company where I work with, so I can't share the files.