Importing data from SharePoint lists into PowerBI [Records & Tables]

[angiepowerbi99]

I have multiple columns that are stored in various ways, some of my data is [Record], [Table] and [Lists]. When i go to expand this out, as there are multiple values in the one row, by doing it manually by clicking the button "Expand" and selecting the heading i am after, it ends up duplicating my records. 

Looks like this: 

What is the best way using Power Query to expand the record/table or list and the join them by using the delimiter "," for concatenating values. 

Previously i have used this below, however as some aren't tables they are [Records] & [Lists]  this doesn't seem to work.

Any help would be greatly appreciated!! 

[v-vpabbu]

Hi @angiepowerbi99,

We would like to follow up to see if the solution provided by @Akash_Varuna resolved your issue. Please let us know if you need any further assistance.
If the user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @angiepowerbi99,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @angiepowerbi99,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Vinay Pabbu

[Akash_Varuna · Accepted solution]

You could use Group By function in power bi power query and then do a Text.Combine function to combine with delimeter

1 Group By function with desired Column
2  In the Grouped Data Create a custom cloumn which extracts the desired data from rows
3 Use Text.Combine() function to combine them concatinate them
4 Keep Only the concatinated result by flatening the grouped one

Or You can use Table.Transform column with To.List function and then use Text.combine function to concatinate

[PwerQueryKees]

Replace the Table.Column by

• Record.Field
  For records
• By removing the function call completely
  For listst

Then do an add column with Text.Combine([FieldName], ",")

I am not at my laptop, so I can't test.