Imported table with lists

Power Query Data Setup
1

I’ve imported data from an API using the Power BI web connector. In the Query Editor I see that most of the data in the table consists of regular data types (text, dates, numericals, etc), but a few columns have lists.

In general, would these lists be a part of the table and therefore should be expanded, or would they likely be other tables that have a relationship with this main table?

The reason I ask is because some of these lists add to the rows in this main table and in some cases create duplicate rows. When expanded, they also add to the number of columns.

I don’t have a sample for this and simply need to know how to treat these lists.

1 Like
2

Hi @powerbideveloper,

You would have to know the source to answer that Q.
.

How you treat them depends on the data they contain and what you need to do with that data…

  • Expanding to new rows will lead to “duplication” of all other fields in that row if there is more than 1 item in that List.
  • Alternatively you could “Extract values” combining them into a single text string (and optionally separating those into new columns (if that makes sense for your data))
  • or aggregate the results into a single value if for example you have a numerical field in that list
  • However if you don’t need the data at all, don’t bring it in or just delete that column

You’ll have to make sense of that…

1 Like
3

Agree with all that @Melissa has stated here, but I do have one additional suggestion.

if the expanded lists are leading to duplicate rows, then probably you are looking at one or more related tables (I have this with some of my source data). In that case, if you determine that one of the additional tables would be useful for your reports, I would suggest using your original query as a source for two queries:

Example:
Bring in your original table - and make it a reference table (turn off the load to data model)

REFERENCE the original table for Q1 - and remove the columns that contain the lists. And do any other necessary transformations.

REFERENCE the original table again for Q2 - this time remove the other columns, except for the index column, and remove any list columns that you don’t want in your data.

don’t forget, it is always possible for you to go back and load data into your report later if you need it - best to avoid bloat if you really don’t have a good use-case for a column of data at this time.

2 Likes