Dynamically create a new table with multiple concatenated columns

[mwllms]

I have a SharePoint list with 60+ comment fields. I want to create a new table with a calculated column for each comment with the following format:

Current Date + Submitter + Comment

Since there are so many comment fields, is there a more efficient way of dynamically creating these columns without having to create each column one at a time? There may be the possibility that more questions will be added in the future, so I would like to find the most effective way to do this if possible.

In addition, the other issue I am running into is that the submitter field is a SharePoint people type and in Power BI it is showing as a record. I am not sure how to get it to show the display name instead.

Any information or guidance would be greatly appreciated! 

[parry2k]

@mwllms Best would be to unpivot the data, and then it will be one column to work with which wil make it very easy. If you can share sample data, will help you get the solution.

I'm not sharepoint/powerbi expert but seems like there are multiple fields in submitter (first name, last name etc), what you get if you expand it  by clicking arrows on the column header.

[mwllms]

Hi @parry2k : Thank you for your help. I am not sure what you mean by unpivot the data. What I am trying to do is combine three columns into one and I am trying to do this process for 60+ columns. For example:

This is an example of what the table looks like. I want to combine:

Date Submitted +Submitter + QuestionNumberComments for each of the 60+ columns in the most efficent way possible.

The end result would look like this:

Do you know of a better way to accomplish this than to create a column on by one?

[parry2k]

@mwllms yes unpivot will make it easier, if possible Please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.

[mwllms]

@parry2k : Unfortunately, I won't be able to share the data because it all contains sensitive company information.  If you have any links to examples or resources on what you are trying to show me that would help out as well.